How do I UPDATE from a SELECT in SQL Server?

ID : 98

viewed : 182

Tags : sqlsql-servertsqlselectsql

Top 5 Answer for How do I UPDATE from a SELECT in SQL Server?

vote vote

92

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 = 'cool' 
vote vote

86

In SQL Server 2008 (or newer), use MERGE

MERGE INTO YourTable T    USING other_table S        ON T.id = S.id          AND S.tsql = 'cool' WHEN MATCHED THEN    UPDATE        SET col1 = S.col1,            col2 = S.col2; 

Alternatively:

MERGE INTO YourTable T    USING (           SELECT id, col1, col2              FROM other_table             WHERE tsql = 'cool'          ) S       ON T.id = S.id WHEN MATCHED THEN    UPDATE        SET col1 = S.col1,            col2 = S.col2; 
vote vote

79

UPDATE YourTable  SET Col1 = OtherTable.Col1,      Col2 = OtherTable.Col2  FROM (     SELECT ID, Col1, Col2      FROM other_table) AS OtherTable WHERE      OtherTable.ID = YourTable.ID 
vote vote

68

I'd modify Robin's excellent answer to the following:

UPDATE Table SET Table.col1 = other_table.col1,  Table.col2 = other_table.col2 FROM     Table INNER JOIN other_table ON Table.id = other_table.id WHERE     Table.col1 != other_table.col1 OR Table.col2 != other_table.col2 OR (     other_table.col1 IS NOT NULL     AND Table.col1 IS NULL ) OR (     other_table.col2 IS NOT NULL     AND Table.col2 IS NULL ) 

Without a WHERE clause, you'll affect even rows that don't need to be affected, which could (possibly) cause index recalculation or fire triggers that really shouldn't have been fired.

vote vote

54

One way

UPDATE t  SET t.col1 = o.col1,      t.col2 = o.col2 FROM      other_table o    JOIN      t ON t.id = o.id WHERE      o.sql = 'cool' 

Top 3 video Explaining How do I UPDATE from a SELECT in SQL Server?

Related QUESTION?