# How do SQL MERGE statements work?

Source: https://zilliz.com/ai-faq/how-do-sql-merge-statements-work
Author: zilliz

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:

```sql
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.