Sql merge statement with example
by Yogi P - December 8, 2022
SQL MERGE statement
The syntax for the MERGE statement is:
MERGE INTO target_table AS TUSING source_table AS SON conditionWHEN MATCHED THENUPDATESET col1 = value1, col2 = value2, ...WHEN NOT MATCHED THENINSERT (col1, col2, ...) VALUES (value1, value2, ...)WHEN NOT MATCHED BY SOURCE THENDELETE;Here, target_table is the table that you want to update, insert, or delete data in, and source_table is the table that you are using as the source of data. The ON clause specifies the condition that must be met in order for the MERGE statement to take action (i.e. update, insert, or delete data).
The WHEN MATCHED clause specifies the action to take if a matching record is found in the target table (i.e. update the record with new data from the source table).
The WHEN NOT MATCHED clause specifies the action to take if a matching record is not found in the target table (i.e. insert a new record from the source table). And the WHEN NOT MATCHED BY SOURCE clause specifies the action to take if a record exists in the target table but not in the source table (i.e. delete the record from the target table).
Here is an example of using MERGE to combine data from two tables:
If we want to combine the data from these two tables into a single table, we can use the MERGE statement:
MERGE INTO students AS SUSING courses AS CON S.course = C.course_nameWHEN MATCHED THENUPDATESET S.course_id = C.idWHEN NOT MATCHED THENINSERT (name, course, course_id) VALUES (S.name, S.course, C.id)This would produce the following result:
Pin It
