Introduction

In SQL Server it's a well know fact that is possible to INSERT into a table using a SELECT statement.

In this tutorial we will show you that is possible to update one table from another using SELECT.

Simple solution

The following solution works with SQL Server and was not tested with other databases.

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Table_To_Update AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id

The query is using the 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.

Solution using merge

This solution only works with SQL Server since it uses the 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;

If you want to learn more about merge, check here.

Solution for MySQL

This tutorial was for SQL Server, but just in case you are curious:

UPDATE table_a, table_b
SET table_a.color = table_b.color
WHERE table_a.id = table_b.foreign_id