How to UPDATE from SELECT in SQL Server
Under most circumstances, SQL updates are performed using direct references to a particular table (
UPDATE books SET books.title = 'The Hobbit' WHERE books.id = 1). Yet, on occasion, it may prove beneficial to alter the contents of a table indirectly, by using a subset of data obtained from secondary query statement.
UPDATE using a secondary
SELECT statement can be accomplished in one of two ways, primarily depending upon which version of SQL Server you are using. We'll briefly explore both options so you can find what works best for you.
Using INNER JOINS
For all SQL Server installations, the most basic method of performing this action is to use an
INNER JOIN, whereby values in the columns of two different tables are compared to one another.
UPDATE books SET books.primary_author = authors.name FROM books INNER JOIN authors ON books.author_id = authors.id WHERE books.title = 'The Hobbit'
In the above example, we're
books.primary_author field to match the
authors.name for 'The Hobbit' by
JOINING both tables in the query to their respective, matching values of
Using MERGE to UPDATE and INSERT Simultaneously
For SQL Server 2008 and newer, Microsoft introduced the exceptionally useful
MERGE operation which is similar to the above
INNER JOIN method, but
MERGE attempts to perform both an
UPDATE and an
INSERT command together. This effectively synchronizes the two tables based on the query performed, updating and inserting records as necessary for the two to match.
MERGE INTO books USING authors ON books.author_id = authors.id WHEN MATCHED THEN UPDATE SET books.primary_author = authors.name WHEN NOT MATCHED THEN INSERT (books.author_id, books.primary_author) VALUES (authors.id, authors.name)
The full query when using
MERGE is certainly a bit more complex then that of a basic
INNER JOIN, but once you grasp how the operation functions, you'll quickly understand how powerful this capability can truly be.
The first few lines are rather self-explanatory:
MERGE INTO books USING authors ON books.author_id = authors.id
We want to
MERGE INTO (
books table by using the secondary
authors table, and we're matching the two based on the same
books.author_id = authors.id comparison.
MERGE command differs is in the branching logic that follows.
WHEN MATCHED THEN UPDATE SET books.primary_author = authors.name
Here we're asking SQL to perform an action only when records
MATCHED -- when an existing record is found. In that case, we perform a standard
UPDATE just as we did before, setting the
books.primary_author field to equal the
Finally, if the query discovers a matching comparative record that doesn't exist, we instead perform an
WHEN NOT MATCHED THEN INSERT (books.author_id, books.primary_author) VALUES (authors.id, authors.name)
Here we're simply asking SQL to
INSERT a new record into the
books table and passing along the values for the
primary_author fields, grabbed from the associated
authors table record.
The end result of our
MERGE statement is that for every author in the
authors table, we verify whether a corresponding book exists in
books. If a record is found, we ensure
books.primary_author is set using
UPDATE, and where no match is found, we add a new record to
With that, you should have a solid understanding of two different methods that can be used to
UPDATE records in SQL by using secondary, comparative