How to upsert in Sql using Merge statement with where clause

Question

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.

Answer 1

your code seems correct, if you comment these: