SQL MERGE statements provide a powerful way to perform insert, update, or delete operations on a target table based on the results from a source table. This single statement helps to synchronize the two tables by merging data efficiently. For instance, you might have a target table with customer information and a source table with updated details. The MERGE statement can be used to update existing records if there is a match, insert new records if there’s no match in the target, or even delete records based on specific conditions.
The syntax of a MERGE statement typically starts with the MERGE INTO
keyword followed by the target table. Then, you specify the USING
clause to define the source table. A matching condition is established using the ON
clause. Inside the statement, you outline what action to take in the event of a match with the WHEN MATCHED
clause, which often consists of an UPDATE
command. Conversely, you can use the WHEN NOT MATCHED
clause to handle cases where no match is found, usually resulting in an INSERT
operation.
Here's a basic example to illustrate. Suppose you have a target table called Products
and a source table named NewProducts
. You want to update the price of products that exist in both tables and insert any new products from NewProducts
that do not yet exist in Products
. The MERGE statement could look like this:
MERGE INTO Products AS target
USING NewProducts AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN
UPDATE SET target.Price = source.Price
WHEN NOT MATCHED THEN
INSERT (ProductID, ProductName, Price)
VALUES (source.ProductID, source.ProductName, source.Price);
In this case, if the ProductID
from NewProducts
exists in Products
, the price will be updated; if it does not exist, a new product will be inserted with the provided details. This makes the MERGE statement not only versatile but also a cleaner way to handle complex data updates, ensuring your database remains consistent and up-to-date with minimal code.