How do I UPDATE from a SELECT in SQL Server?

Created by Robin DayLinked to 29.5m issues across 196 teams

tl;dr

In order to update a table from a select statement in SQL Server, you must first join the two tables together. This is done by using an INNER JOIN. This will join the two tables together based on a common id.

Once the tables are joined, you can then use the UPDATE statement to set the values of the columns in the first table to the values of the columns in the second table. You can also add a WHERE clause to the statement to specify which rows should be updated.

For example, if you wanted to update the columns col1 and col2 in Table_A to the values of col1 and col2 in Table_B, you would use the following statement:

UPDATE Table_A SET Table_A.col1 = Table_B.col1, Table_A.col2 = Table_B.col2 FROM Some_Table AS Table_A INNER JOIN Other_Table AS Table_B ON Table_A.id = Table_B.id WHERE Table_A.col3 = 'fun';