UPDATE from SELECT in SQL Server  

This can be accomplished using an INNER JOIN, where you compare the values in the columns of two different tables. Here is an example ...

Consider that you need to update the movies.primary_director field to match the directors.name for 'The Great Train Robbery' by joining both tables in the query to their respective, matching values of directors.id and movies.director_id.

UPDATE movies
SET movies.primary_director = directors.name
FROM movies INNER JOIN directors ON movies.director_id = directors.id
WHERE movies.title = 'The Great Train Robbery'

In SQL Server 2008 and above, there is a MERGE statement that lets you perform these operations:

a. Conditionally insert or update rows in a target table: If a row exists in a target table, update one or more columns. Otherwise, insert the data as a new row.

b. Synchronise two tables: Insert or update rows in a target table based on differences with the source data.

Let's rewrite the Ayano's example given above:

MERGE INTO movies USING directors
ON movies.director_id = directors.id
WHEN MATCHED THEN
  UPDATE SET movies.primary_director = directors.name
WHEN NOT MATCHED THEN 
  INSERT (movies.director_id, movies.primary_director) VALUES (directors.id, directors.name)

MERGE INTO updates the movies table by using the secondary directors table, and the two are matched when movies.director_id = directors.id. In other words, if an existing record is found, you perform a standard UPDATE, by setting the movies.primary_director field to equal the directors.name field.

If a comparative record is not found, you instead perform an INSERT into the movies table and providing the values for the director_id and primary_director fields, taken from the associated directors table record.