How do I UPDATE from a SELECT in SQL Server?

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' 

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; 

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

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.

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'