When modifying one table based on the contents of another it may be tempting to simply use the merge statement, but before you throw away all other forms of modifying data, I think it’s important for you to know the difference between merge and update.
Table of contents
What is the Difference Between MERGE and UPDATE?
Both the MERGE and UPDATE statements are designed to modify data in one table based on data from another, but MERGE can do much more.
Whereas UPDATE can only modify column values you can use the MERGE statement to synchronize all data changes such as removal and addition of row. The MERGE statement is structured to handle all three operations, INSERT, UPDATE, and DELETE, in one command.
When you just need to UPDATE data you’re better off using the UPDATE statement as the MERGE statement is built to handle several matching scenarios, it is more complex and may run less efficiently. Per MSDN:
The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements. (MSDN: MERGE Transact-SQL)
Here is a side-by-side comparison of the MERGE and UPDATE statements:
In this side by side comparison you can see the similarities key areas of these statements:
- Target – They both specify a target data source.
- Source – Each has a source of information to drive the update.
- Merge Condition – In both you find a means to match rows in one table to the next. In the MERGE statement, this is called the merge condition. In an INNER JOIN, it is called a join condition.
- Column Update – Each has a SET clause to specify which columns are updated.
Key Differences between MERGE and UPDATE
First Difference
With MERGE, you’re able to combine update, delete, and insert command into one statement. This is because the MERGE statement uses a WHEN clause to determine the course of action to take on the match.
With a MERGE, you can take different actions based on the rows matching or not matching the target or source. With the updated, you’re only updating rows that match.
Consider if you want to do synchronize all chance from one table to the next. In this case merge become more efficient as less passes through the data.
MERGE esqlProductTarget T
USING esqlProductSource S
ON (S.ProductID = T.ProductID)
WHEN MATCHED
THEN UPDATE
SET T.Name = S.Name,
T.ProductNumber = S.ProductNumber,
T.Color = S.Color
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, Name, ProductNumber, Color)
VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Read More: SQL MERGE Statement >>
Second Difference
Another difference is I feel the MERGE statement is easier to read. Look again at the comparison above. I think the MERGE command “flows” easier. You’re able to read it top down. The key items, such as source, target, merge conditions, and the set clause are in order top down.
When you read the UPDATE statement, the key items are scattered. And it takes a bit of training to understand the INNER JOIN.
Third Difference between Merge and Update
The UPDATE statement will most likely be more efficient than a MERGE if the all you are doing is updating rows. Given the complex nature of the MERGE command’s match condition, it can result in more overhead to process the source and target rows. However, when you need to do more than one operation, the MERGE command is most likely a better choice, as you are only making one pass through the data as opposed to multiple passes, one for each separate UPDATE, INSERT, or DELETE command, through the source data.
Read More: SQL UPDATE Statement >>
Want to learn more about UPDATE and Merge? If so, I would recommend ready my article Introduction to SQL Server Data Modification Statements.
