tutorial SQL Server - How do I UPDATE from a SELECT in SQL Server?

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

I

Code:

NSERT 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:

Code:

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

Answers

Code:

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'

If you are editing the the link between tables (SET Table.other_table_id = @NewValue) then change the ON statement to something like ON Table.id = @IdToEdit AND other_table.id = @NewValue – Trisped Oct 24 '12 at 18:41
Isn't this missing the WHERE clause in the question? I don't have a server on this system to test it but wouldn't you be able to add that to the ON like: ON Table.id = other_table.id AND other_table.sql='cool'? Or am I misinterpreting the question? – J V May 31 '13 at 18:15
This works by using UPDATE to iterate over the INNER JOIN. As such the ON functions as your WHERE clause and the INNER JOIN skips records that are not found in the JOINed table. Adding a WHERE clause would limit the result set of the JOINed table as well. @Roger Ray what version of MySQL and what was your query, as this DOES infact function as stated. – fyrye Sep 6 '13 at 17:14
@RogerRay, this question is about Microsoft SQL Server. Unfortunately, the syntax between various SQL implementations can vary. – Charles Wood Nov 26 '13 at 17:12
Somewhat related, I often like to write my UPDATE queries as SELECT statements first so that I can see the data that will be updated before I execute. Sebastian covers a technique for this in a recent blog post: sqlity.net/en/2867/update-from-select – dennislloydjr Aug 21 '15 at 19:48