How to UPDATE from SELECT (or MERGE) in SQL Server.
---
title: "How to UPDATE from SELECT (or MERGE) in SQL Server"
author: chartio.com
date: March 27, 2018
source: https://chartio.com/resources/tutorials/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.
Performing an `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][1]
are compared to one another.
```sql
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 `UPDATING` the `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 `authors.id` and `books.author_id`.
## Using MERGE to UPDATE and INSERT Simultaneously
For SQL Server 2008 and newer, Microsoft introduced the exceptionally useful
[`MERGE`][2] 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.
```sql
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:
```sql
MERGE INTO
books
USING
authors
ON
books.author_id = authors.id
```
We want to `MERGE INTO` (`UPDATE`/`INSERT`) the `books` table by using the
secondary `authors` table, and we're matching the two based on the same
`books.author_id = authors.id` comparison.
Where the `MERGE` command differs is in the branching logic that follows.
```sql
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
`authors.name` field.
Finally, if the query discovers a matching comparative record that **doesn't** exist, we instead perform an `INSERT`.
```sql
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 `author_id` and `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 `books`.
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 `SELECT`
statements.
[1]: https://technet.microsoft.com/en-us/library/ms190014.aspx
[2]: https://technet.microsoft.com/en-us/library/bb510625.aspx