SQL Server UPDATE from SELECT

Question

Thanks in advance guys.

Been searching quite a bit for this and I'm either searching badly, or there isn't much out there to explain it.

In SQL server you can insert into a table using a select statement.

INSERT INTO table(col,col2,col3)
SELECT col,col2,col3 FROM other_table (in this case is a temp table) WHERE sql = 'cool'

Would be awesome, if i could Update via a select aswell. So I have a Temp Table that has the values and I want to update another table using those values. Something like this:

UPDATE Table SET col1,col2
SELECT col1,col2 FROM other_table WHERE sql = 'cool'
WHERE Table.id = other_table.id

Thanks in advance! James

Answer 1

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

Answer 2

One way

UPDATE t SET  t.col1 = o.col1,
t.col2 = o.col2
FROM other_table o 
join Table t on t.id = o.id
WHERE o.sql = 'cool'