An Intro to SQL Part 4 Merge Statements

By Charles McDowell

Comptia Certified Linux Networking Professional.

Cisco Certified Networking Technician.

Microsoft Technology Associate Database Administration

View fullsize

“In the future, you’ll simply jump into your car, turn on the Internet, turn on a movie and sit back and relax and turn on the automatic pilot, and the car will drive itself.”

— Michio Kaku

What does the Merge statement do?

The Merge statement takes the results of a source table and either inserts, deletes or updates a target table based on those results.  

When is it best to use a Merge statement?

Merge statements are best used when combining two tables with a complex mixture of matching characteristics. When updating a table based on the rows of another table, we can improve the performance and scalability of our query just by using INSERT, UPDATE, and DELETE statements. However, this will mean we’ll have to type out 3 separate statements. Merge allows us to do all three at the same time.

Example:

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

 What are some things to be Cautious of when using Merge?

Make sure to specify which columns of the target table will be compared to the source table columns. You can have two WHEN MATCHED clauses and WHEN NOT MATCHED BY SOURCE clauses at most. One for updating and one for deleting, but they can’t affect the same row. There can only be WHEN NOT MATCHED clause.