My first attempt, which was incorrect, was this: The problem with this update statement was that I realized it would update all 5 records instead of just the 2 records accounting wanted to update; this would result in 103 and 104 being updated to the updated amounts, but all other amounts wiped out to null value.To remedy that, we could throw in a where clause to the update statement, as below: -- Better example, but still inefficient update master_table m set m.invoice_amount=( select a.updated_invoice_amount from data_from_accounting a where m.job_number=a.job_number ) where m.job_number in( select a2.job_number from data_from_accounting a2 where m.job_number=a2.job_number ); This would correctly update the records I wanted to update while leaving the others alone, but this query is a little inefficient as it needs to go through the data from accounting twice.
In this scenario, accounting only sent me two records, but in the real life situation I ran into, it was over 10,000 records.
Also, this scenario's master table has only 5 records; my real master table has close to 1,000,000 records.
Below is the basic syntax for the UPDATE statement: This is not the complete syntax of the update statement.
If you want to review the complete syntax of the UPDATE statement then please refer to Books Online.
Thus, we need an update statement that is efficient.