MS SQL: Updating columns to match another column (different tables)

Updating can be kind of tricky and time consuming if there are a lot of rows being affected.  The task is especially cumbersome, or seems so, when updating a particular column from one table with data in a column from another table.

The simple SQL query to accomplish this is:


update t1
set t1.c1=t2.c1,
t1.c2=t2.c2
from table1 t1
inner join table2 t2
on t1.col=t2.col

In this script example, we are updating table 1 (t1) by setting a column (t1.c1) to the value of the column of table 2's column (t2.c1) - the next line shows another column being updated with the same table two, but using a different column.


Next, we are telling it to take this information from table 1 t1 - the t1 is the alias of table 1.  Then we join table 2 (t2) into the query with the INNER JOIN statement.  The join basically says that table2 is using alias "t2" and that we want column 1 in table 1 to equal column 1 in table 2 for our equality condition to be met.  So, if column 1 in table 1 is apples and column 1 in table 2 is apples and the column we are updating is "price", then we would see something like this:

update t1

set t1.price = t2.price

from table1 t1

inner join table2 t2

on t1.apples=t2.apples

Pretty simple stuff.  Drop me a comment if you have any questions.