This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Using MERGE and MATCHED in SQL
Learn how to MERGE rows from two tables into one combined table, and how to remove unwanted data from both tables.

Posted by Sam Lowrie on 19 June 2018

In this blog

WHEN MATCHED
WHEN NOT MATCHED BY SOURCE
WHEN NOT MATCHED BY TARGET
OUTPUT

Using MERGE and MATCHED in SQL

I've recently got the lease for a movie mega store and have decided to consolidate two of my stores into one. In my records I have the names, sales quantity and prices for each film (and you can have these too, if you run the following script):

--Creates a temp table holding prices and sales for store 1

CREATE TABLE #Store1

(Sales smallint, Film_Title varchar(100), Price Decimal(4,2))

Insert into #Store1

Values (2, 'Wonder Woman', 4.99), (1, 'Casino Royale', 2.99), (5, 'Wasabi', 7.99), (0, 'Pulp Fiction', 10.99)

--Creates a temp table holding prices and sales for store 2

CREATE TABLE #Store2

(Sales smallint, Film_Title varchar(100), Price Decimal(4,2))

Insert into #Store2

Values (3, 'Thor: Ragnarock', 14.99), (4, 'Casino Royale', 2.99), (2, 'Dr Who', 7.99), (0, 'Paw Patrol', 20.99), (3, 'Wasabi', 7.99)

To consolidate these tables I can use MERGE - this is similar to creating a join between the tables but has the scope to do more:

--Indicate the table to update, with alias

MERGE #store1 AS S1

--Indicate the secondary table, with alias

USING #Store2 AS S2

--Join the two tables based on a common field

ON S1.Film_Title = S2.Film_Title

In conjunction with MERGE we can use the following commands:

WHEN MATCHED THEN

WHEN NOT MATCHED BY SOURCE THEN

WHEN NOT MATCHED BY TARGET THEN

WHEN MATCHED

WHEN MATCHED will let me do something when two rows in the tables overlap (like an inner join). An example might include updating the Sales column in #Store1 to include sales made in #Store2:

MERGE #store1 AS S1

USING #Store2 AS S2

ON S1.Film_Title = S2.Film_Title

WHEN MATCHED THEN

UPDATE

-- semi colon needed for MERGE

SET S1.Sales = s1.sales + s2.sales;

Now any film that appears in both tables will have their Sales combined in the #Store1 Sales column:

Store1 had sold 5 copies of Wasabi whilst Store2 sold only 3 copies. The total sales are now listed in the Store1 table as 8.

WHEN NOT MATCHED BY SOURCE

Sadly Pulp Fiction must be a dying cult classic, and nobody wants to watch it. How could I get rid of it? First some new terminology:

The MERGE table (Store1) is referred to as the TARGET whilst the USING table is the SOURCE (Store2).

Pulp Fiction only exists in the TARGET (Store1) meaning there is no corresponding value in the SOURCE table. In code terms you can write:

MERGE #store1 AS S1

USING #Store2 AS S2

ON S1.Film_Title = S2.Film_Title

WHEN MATCHED THEN

UPDATE

SET S1.Sales = s1.sales + s2.sales

WHEN NOT MATCHED BY SOURCE AND

S1.SALES = 0 THEN

DELETE;

The AND can be followed by a condition and outcome, such as to DELETE any unmatched rows that have a SALES value of 0. This means Store1 now contains:

What? What happened to Pulp Fiction? Say what again......I dare you.

The opposite would be copying any films not found in the TARGET from the SOURCE table.

WHEN NOT MATCHED BY TARGET

The SOURCE table (Store2) has 3 films not found in my TARGET table (Store1). I want to copy these films if they have sold any copies :

MERGE #store1 AS S1

USING #Store2 AS S2

ON S1.Film_Title = S2.Film_Title

WHEN MATCHED THEN

UPDATE

SET S1.Sales = s1.sales + s2.sales

WHEN NOT MATCHED BY SOURCE AND

S1.SALES = 0 THEN

DELETE

WHEN NOT MATCHED BY TARGET AND

s2.sales > 0 then

insert (Sales, Film_Title, Price)

values(s2.sales, s2.Film_Title, s2.price)

;

This means that if any films sold in Store2 weren't sold in Store1 they have been added to the available stock:

We have removed any films that haven't sold (and included any that have) from both stores into one combined table.

My stock is looking consolidated, but what changes did I make? When using MERGE it is possible to capture all the changes made and present then in a table.

OUTPUT

The OUTPUT clause gives us access to the INSERTED and DELETED tables, and using the $action class we can filter/show the class type:

MERGE #store1 AS S1

USING #Store2 AS S2

ON S1.Film_Title = S2.Film_Title

WHEN MATCHED THEN

UPDATE

SET S1.Sales = s1.sales + s2.sales

WHEN NOT MATCHED BY SOURCE AND

S1.SALES = 0 THEN

DELETE

WHEN NOT MATCHED BY TARGET AND

s2.sales > 0 then

insert (Sales, Film_Title, Price)

values(s2.sales, s2.Film_Title, s2.price)

OUTPUT

$action,

inserted.*, deleted.*;

This gives a list of all columns affected by INSERT, DELETE or UPDATE:

Whilst it isn't necessary to include $action, it would make life harder working out what happened if you omitted it!

The first set of column names are from the INSERTED table and the second set are from the DELETED table. All film records I changed are now in one place!

Try selecting specific columns from the INSERTED table instead of using *. Also try OUTPUT INTO to store data into a table variable.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using MERGE and MATCHED in SQL

WHEN MATCHED

WHEN NOT MATCHED BY SOURCE

WHEN NOT MATCHED BY TARGET

OUTPUT

Head office

London

Manchester