| Downloads |
|---|
| 26057.zip |
How can I update three columns in table A from three columns in table B in one UPDATE statement?
For this question, you can use the power of relational algebra. The sample code in Listing 1 demonstrates that you can use the FROM clause combined with a JOIN operation to update specified columns from a second table. When you design relational expressions, you have to decide whether you want a single row to match multiple rows (in a one-to-many—1:M—relationship) or you want many rows to match a single row in the joined table (i.e., you want to update all rows). In a 1:M relationship, SQL Server always uses the last row it finds for the update. However, you have no way of influencing what the last row will be, and on multiprocessor machines where the query might be parallelized, the last row might differ from execution to execution. Therefore, we recommend that you avoid the 1:M relationship.
Also, if the table you're updating is the same as the table in the FROM clause and the FROM clause contains only one reference to the table, an alias might not be specified. If the table you're updating appears more than once in the FROM clause, only one reference to the table can omit a table alias; all other references to the table must include a table alias.
