How can I update/modify the contents of a table, by using a subset of data obtained from secondary SQL query statement?

2 Answers

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 for 'The Great Train Robbery' by joining both tables in the query to their respective, matching values of and movies.director_id.

UPDATE movies
SET movies.primary_director =
FROM movies INNER JOIN directors ON movies.director_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 =
  UPDATE SET movies.primary_director =
  INSERT (movies.director_id, movies.primary_director) VALUES (,

MERGE INTO updates the movies table by using the secondary directors table, and the two are matched when movies.director_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 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.