MERGE

SQL Server

Example

--Creating a Table
CREATE TABLE Student
(
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Age INT
);

--Inserting Data into the Table
INSERT INTO Student
VALUES  (1, 'John', 16),
        (2, 'Sally', 18),
        (3, 'Mark', 17);

--Making a Duplicate Table with Updated Values
CREATE TABLE NewStudentData
(
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Age INT
);

INSERT INTO NewStudentData
VALUES  (1, 'John', 17),
        (2, 'Sally', 19),
        (4, 'Luke', 16);

--MERGE Statement
MERGE INTO Student AS Target
USING (SELECT * FROM NewStudentData) AS Source
ON (Target.StudentID = Source.StudentID)

--Operations
WHEN MATCHED THEN
    UPDATE SET Target.Age = Source.Age
WHEN NOT MATCHED BY TARGET THEN
    INSERT (StudentID, Name, Age) VALUES (Source.StudentID, Source.Name, Source.Age);

Output

(1 row(s) affected)
(3 row(s) affected)
(1 row(s) affected)
(3 row(s) affected)
(4 row(s) affected)

Explanation

The MERGE statement is a combination of the INSERT, DELETE and UPDATE commands to simultaneously perform these operations in our Student table. In this example, first, two tables namely Student and NewStudentData are created. Then, the values from NewStudentData are merged into the Student table. If the StudentID exists in both tables, the Age is updated in the Student table. If the StudentID is found only in the NewStudentData table, a new row is inserted in the Student table. The output tells us the number of rows affected in each operation.

Oracle

Example

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

Output

2 rows merged.

Explanation

In the given example, the MERGE statement is used to synchronize the target_table with the source_table. The ON clause defines the condition for the match, in this case, when the column1 in both tables is equal.

When a match is found, the MATCHED clause is executed updating column2 in the target_table with the value from source_table.

If no match is found, the NOT MATCHED clause is executed, where a new row is inserted into the target_table with the column1 and column2 values from source_table.

The output ‘2 rows merged.’ indicates that the operation affected two rows in the target_table.