How to upsert in Sql using Merge statement with where clause
0
votes
1
answer
321
views
CREATE TABLE SourceProducts(
ProductID INT,
ProductName VARCHAR(50),
Price DECIMAL(9,2)
)
GO
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(1,'Table',100)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(2,'Desk',80)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(3,'Chair',50)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(4,'Computer',300)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(7,'Monitor',400)
GO
CREATE TABLE TargetProducts(
ProductID INT,
ProductName VARCHAR(50),
Price DECIMAL(9,2)
)
GO
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(1,'Table',100)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(2,'Desk',180)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(5,'Bed',50)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(6,'Cupboard',300)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(7,'Monitor',900)
GO
Currently I am using the above data as a sample to understand how merge functionality operates.
I am using the below code to upsert , but I have a condition that it should update only monitor not desk (I may replace this with other condition like todays date)
MERGE TargetProducts AS Target
USING SourceProducts AS Source
ON Source.ProductID = Target.ProductID
-- For Updates
WHEN MATCHED
THEN UPDATE SET
Target.ProductName = Source.ProductName,
Target.Price = Source.Price
-- For Inserts
WHEN NOT MATCHED BY Target THEN
INSERT (ProductID,ProductName, Price)
VALUES (Source.ProductID,Source.ProductName, Source.Price);
MERGE INTO TargetProducts AS target
USING SourceProducts AS source
ON target.ProductID = source.ProductID
WHEN MATCHED
AND source.ProductName = 'Monitor'
THEN
-- Update existing rows
UPDATE SET
Target.ProductName = Source.ProductName,
Target.Price = Source.Price
WHEN NOT MATCHED
THEN
-- Insert new rows
INSERT (ProductID,ProductName, Price)
VALUES (Source.ProductID,Source.ProductName, Source.Price);
However , the condition isn't making any difference in this case.
Where I am going wrong.
Asked by amit agarwal
(11 rep)
May 27, 2024, 07:11 AM
Last activity: May 27, 2024, 11:45 AM
Last activity: May 27, 2024, 11:45 AM