MERGE
statement can be use to modify data in a target table based on data in a source
table. Merge joins the target to the source by using a column common in both
tables, such as parent-child key column. You can then insert, modify, or delete
data from the target table, all in one statement, according to how the rows
match up as a result of the join.
The MERGE
statement comes up with many clauses that facilitate the different types of
data modifications.
In the first
part of article we will look the When MATCHED clause later in next part we will
see When NOT MATCHED [Source]/[Target] .
To facilitate
our article, let’s take an example and demonstrate how it work, we are here
creating two tables MarblesCounts_KancheKhelega as target table and
MarblePurchased_ChalKancheLene
as source table
create table dbo.MarblesCounts_KancheKhelega -- target
(
marble_id int
not null primary key,
marble_color varchar(40) not null ,
marble_count int
not null
constraint
MarbleCount_Default_1 default 0
);
Command(s) completed successfully.
create table dbo.MarblePurchased_ChalKancheLene -- source
(
marble_id int
not null primary key,
marble_color varchar(40) not null,
marble_count int
not null
constraint
MarbleCount_Default_2 default 0
);
Tables are in
place let’s inserts few records in it.
insert into MarblesCounts_KancheKhelega values
(1, 'White wale bade Kanche', 6),
(2, 'Pale Yellow wale Dimond
Kanche', 3),
(3, 'Red Ramesh ke kanche', 0),
(5, 'Green Suresh ke churaye
kanche', 0),
(6, 'Purple Kamal se jite
kanche', 0),
(8,
'Blue har game jitane wala lucky kanche', 4);
(6 row(s) affected)
insert
MarblePurchased_ChalKancheLene values
(1, 'White wale bade Kanche', 3),
(3, 'Red Ramesh ke kanche', 0),
(4, 'Grey bade unlucky kanche', 4),
(5, 'Green Suresh ke churaye
kanche', 5),
(7,
'Silver bade expensive Kanche', 8);
(5 row(s) affected)
Querying the
table to see the inserted records.
select * from MarblesCounts_KancheKhelega
marble_id marble_color marble_count
-----------
---------------------------------------- ------------
1 White wale bade Kanche 6
2 Pale Yellow wale Dimond Kanche 3
3 Red Ramesh ke kanche 0
5 Green Suresh ke churaye kanche 0
6 Purple Kamal se jite kanche 0
8
Blue har game jitane wala lucky kanche
4
select * from
MarblePurchased_ChalKancheLene
marble_id marble_color marble_count
-----------
---------------------------------------- ------------
1 White wale bade Kanche 3
3 Red Ramesh ke kanche 0
4 Grey bade unlucky kanche 4
5 Green Suresh ke churaye kanche 5
7
Silver bade expensive Kanche
8
Since we have table with data pupulated, now it
is the time to jump into MERGE and see how it works
WHEN MATCHED Clause
The first MERGE clause
we’ll look at is WHEN MATCHED. You should use this clause when you want to
update or delete rows in the target table that match rows in the source table.
Rows are considered matching when the joined column values are the same.
For example, if the marble_id value in the MarblesCounts_KancheKhelega
table matches the marble_id value in the MarblePurchased_ChalKancheLene
table, the rows are considered to match, regardless of the other values in the
matching rows. When rows do match, you can use the WHEN MATCHED clause to
modify data in the target table. Lets look at an example to demonstrate how
this works.
In the following MERGE statement, I join the MarblesCounts_KancheKhelega
table (the target) to the MarblePurchased_ChalKancheLene table
(the source) and then use a WHEN MATCHED clause to update the marble_count column in the target table:
merge
MarblesCounts_KancheKhelega kc
using
MarblePurchased_ChalKancheLene ckk
on kc.marble_id = ckk.marble_id
when MATCHED then
update
set kc.marble_count = kc.marble_count + ckk.marble_count;
select * from
MarblesCounts_KancheKhelega;
marble_id marble_color marble_count
-----------
---------------------------------------- ------------
1 White wale bade Kanche 9
2 Pale Yellow wale Dimond Kanche 3
3 Red Ramesh ke kanche 0
5 Green Suresh ke churaye kanche 5
6 Purple Kamal se jite kanche 0
8 Blue har game jitane wala lucky
kanche 4
(6 row(s) affected)
Suppose you
want to remove from the MarblesCounts_KancheKhelega table any marble whose Quantity
value is 0 in both the target and source tables. You can easily delete such
rows by adding a second WHEN MATCHED clause to your MATCH statement, as shown
in the following example
merge
MarblesCounts_KancheKhelega kc
using
MarblePurchased_ChalKancheLene ckk
on kc.marble_id = ckk.marble_id
when MATCHED AND
kc.marble_count
+ ckk.marble_count
= 0 then
delete
when MATCHED then
update
set kc.marble_count = kc.marble_count + ckk.marble_count;
select * from
MarblesCounts_KancheKhelega;
marble_id marble_color marble_count
-----------
---------------------------------------- ------------
1 White wale bade Kanche 12
2 Pale Yellow wale Dimond Kanche 3
5 Green Suresh ke churaye kanche 10
6 Purple Kamal se jite kanche 0
8 Blue har game jitane wala lucky
kanche 4
(5 row(s) affected)
So here we can see those records whose marble_id
matched in both the table and whose sum of marble count in MarblesCounts_KancheKhelega
and MarblePurchased_ChalKancheLene are removed.
Programming is the fuel of Saturday and Sunday
Post Reference: Vikram Aristocratic Elfin Share
