SQL: Update Multiple Columns From Subquery

The UPDATE statement is pretty straightforward to use, making really easy to change the value of one or more columns in your table.

Sometimes your updates may require a bit more of complexity, like the use of a subquery to update your columns from another table.

Suppose you want to change the values of the Column1 in Table1, using the values of Column2 in Table2.

You can do that using a JOIN on a common field:

UPDATE t1  -- alias of the target table
SET Column1 = t2.Column2
FROM Table1 t1 -- table 1 alias definition
INNER JOIN Table2 t2 -- table 2 alias definition
    ON t2.CommonField = t1.CommonField
WHERE [Condition]
AND [Other conditions..]

You can omit the WHERE and AND clauses if you haven’t got any.

Doubts? Feel free to leave a comment!