SQL MERGE (UPSERT) Tutorial with Examples

The MERGE statement in SQL allows you to perform UPSERT operations — that is, insert new data if it doesn't exist or update existing data if it does. This is very useful for maintaining current data in your database efficiently. Below is a detailed explanation with example data.

Why Use MERGE?

To automate insert/update logic in a single statement.
Reduce the need for multiple queries.
Ensure data consistency and currentness.

Basic Syntax of MERGE


MERGE INTO target_table AS target
USING source_table AS source
ON target.key_column = source.key_column
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1,
               target.column2 = source.column2
WHEN NOT MATCHED THEN
    INSERT (column1, column2)
    VALUES (source.column1, source.column2);

Example Scenario

Suppose you manage a student database. You want to update email addresses for existing students and add new students when they aren't in the database yet.

Initial Data in the Database:

student_id	name	email
1	Rana	rana@oldemail.com
2	Sadia	sadia@oldemail.com

New Data to Merge (Source Data):

student_id	name	email
2	Sadia	sadia@newemail.com
3	Ali	ali@newmail.com

MERGE Query to Perform UPSERT:


MERGE INTO students AS target
USING (
  SELECT 2 AS student_id, 'Sadia' AS name, 'sadia@newemail.com' AS email
  UNION ALL
  SELECT 3, 'Ali', 'ali@newmail.com'
) AS source
ON target.student_id = source.student_id
WHEN MATCHED THEN
    UPDATE SET target.email = source.email
WHEN NOT MATCHED THEN
    INSERT (student_id, name, email)
    VALUES (source.student_id, source.name, source.email);

What Happens After Running This Query?

Student with student_id=2 will have their email updated to sadia@newemail.com.
New student Ali with student_id=3 will be inserted into the database.

Resulting Data in the Database:

student_id	name	email
1	Rana	rana@oldemail.com
2	Sadia	sadia@newemail.com
3	Ali	ali@newmail.com

Summary

The MERGE statement is a powerful tool for efficiently updating or inserting data in your database, saving time and reducing complexity in your SQL operations.

Trust Coding : Programming