How do I UPDATE from a SELECT in SQL Server?

Question

In SQL Server, it's possible to insert into a table using a SELECT statement:

INSERT INTO Table (col1, col2, col3)

SELECT col1, col2, col3 

FROM other_table 

WHERE sql = 'cool'

Is it also possible to update via a SELECT? I have a temporary table containing the values, and would like to update another table using those values. Perhaps something like this:

UPDATE Table SET col1, col2

SELECT col1, col2 

FROM other_table 

WHERE sql = 'cool'

WHERE Table.id = other_table.id

Answer 1

Use this code to UPDATE via SELECT in SQL Server:

UPDATE Table X

SET

Table_X.col1 = Table_Y.col1,

Table_X.col2 = Table_Y.col2,

FROM One_Table AS Table_X INNER JOIN Another_Table as Table_Y ON TableX.id=TableY.id

WHERE TableX.col3=’Cool’

Want to learn SQL from basics! Here's the right video for you on SQL provided by Intellipaat:

OR

UPDATE table1

SET Col1 = a.Col1,

Col2 = a.Col2

FROM ( SELECT ID, Col1, Col2 FROM some_table) a

WHERE a.ID= table1.ID

Hope this helps you!