Sample Header Ad - 728x90

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