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] 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`] 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. : https://technet.microsoft.com/en-us/library/ms190014.aspx : https://technet.microsoft.com/en-us/library/bb510625.aspx