By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE category (
category_id INT PRIMARY KEY,
category_name VARCHAR(255) NOT NULL,
amount DECIMAL(10 , 2 )
);
INSERT INTO category(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
(2,'Comfort Bicycles',25000),
(3,'Cruisers Bicycles',13000),
(4,'Cyclocross Bicycles',10000);
4 rows affected
CREATE TABLE category_staging (
category_id INT PRIMARY KEY,
category_name VARCHAR(255) NOT NULL,
amount DECIMAL(10 , 2 )
);
INSERT INTO category_staging(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
(3,'Cruisers Bicycles',13000),
(4,'Cyclocross Bicycles',20000),
(5,'Electric Bikes',10000),
(6,'Mountain Bikes',10000);
5 rows affected
MERGE category t
USING ( select category_id,max(category_name),sum(amount)
from category_staging s
group by category_id
) as s
ON (s.category_id = t.category_id)
WHEN MATCHED
THEN UPDATE SET
t.category_name = s.category_name,
t.amount = s.amount
WHEN NOT MATCHED BY TARGET
THEN INSERT (category_id, category_name, amount)
VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Msg 8155 Level 16 State 2 Line 6
No column name was specified for column 2 of 's'.
Msg 8155 Level 16 State 2 Line 6
No column name was specified for column 3 of 's'.
MERGE category t
USING ( select category_id,max(category_name),sum(amount)
from category_staging s
group by category_id
) as source(category_id,category_name,amount)
ON (source.category_id = t.category_id)
WHEN MATCHED
THEN UPDATE SET
t.category_name = source.category_name,
t.amount = source.amount
WHEN NOT MATCHED BY TARGET
THEN INSERT (category_id, category_name, amount)
VALUES (source.category_id, source.category_name, source.amount)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
6 rows affected
-- with different names as in select clause inside
MERGE category t
USING ( select category_id,max(category_name),sum(amount)
from category_staging s
group by category_id
) as source(cid,cname,amt)
ON (source.cid = t.category_id)
WHEN MATCHED
THEN UPDATE SET
t.category_name = source.cname,
t.amount = source.amt
WHEN NOT MATCHED BY TARGET
THEN INSERT (category_id, category_name, amount)
VALUES (source.cid, source.cname, source.amt)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
5 rows affected
MERGE category t
USING ( select category_id,max(category_name) as category_name,sum(amount) as amount
from category_staging s
group by category_id
) as source
ON (source.category_id = t.category_id)
WHEN MATCHED
THEN UPDATE SET
t.category_name = source.category_name,
t.amount = source.amount
WHEN NOT MATCHED BY TARGET
THEN INSERT (category_id, category_name, amount)
VALUES (source.category_id, source.category_name, source.amount)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
5 rows affected