BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Sam Lowrie on 21 June 2018
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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.