CODEWITHSUNDEEP
sqlsql-servert-sqlmerge
Pete Alvin
Pete Alvin

Reputation: 4800

T-SQL MERGE: Is this pattern possible?: INSERT (once) if flag true or DELETE if flag false

I wish to use a single SQL statement (i.e., "MERGE") to manage a table representing a mathematical set: insert element if true, else remove.

Using ice cream as an example, I want to add a flavor or remove a flavor from the list based on the @Exist flag below. Is this possible? I've played with it for several hours and either it is syntatically correct and doesn't INSERT the row or I write the query to do what I think needs to be done but then won't compile because the syntax isn't valid. (MSDN MERGE "man" page)

Here is my feable attempt:

CREATE TABLE IceCream(
    Flavor VARCHAR(50) NOT NULL,
    CONSTRAINT AK_Flavor UNIQUE(Flavor)
)

DECLARE @Flavor varchar(50)
DECLARE @Exist bit

set @Flavor='Vanilla'
set @Exist=1

MERGE IceCream AS T
USING IceCream AS S
ON S.Flavor=T.Flavor AND
    S.Flavor=@Flavor AND
    T.Flavor=@Flavor
WHEN NOT MATCHED BY TARGET AND @Exist=1
    THEN INSERT (Flavor) VALUES (@Flavor)
WHEN NOT MATCHED BY SOURCE AND @Exist=0
    THEN DELETE
OUTPUT $action, inserted.*, deleted.*;

Upvotes: 0

Views: 725

Answers (1)

mdisibio
mdisibio

Reputation: 3540

Although there are different ways of accomplishing your goal, this MERGE statement will work:

DECLARE @Flavor varchar(50), @Exist bit;

SET @Flavor = 'Orange';
SET @Exist  = 1;

;WITH TGT AS (SELECT Flavor FROM IceCream WHERE Flavor = @Flavor)
MERGE INTO TGT
USING (VALUES(@Flavor, @Exist)) AS SRC(Flavor, Exist)
   ON TGT.Flavor = SRC.Flavor
WHEN MATCHED AND SRC.Exist = 0
     THEN DELETE
WHEN NOT MATCHED BY TARGET AND SRC.Exist = 1
     THEN INSERT (Flavor) VALUES (SRC.Flavor);

Start with an initial list:

INSERT INTO IceCream VALUES ('Vanilla'), ('Chocolate'), ('Strawberry')

And testing with a few different pairs (Flavor, Exist):

[Orange,1]
----------
Chocolate, Orange, Strawberry, Vanilla

[Rasberry,0]
----------
Chocolate, Orange, Strawberry, Vanilla

[Vanilla,0]
----------
Chocolate, Orange, Strawberry

[Chocolate,1]
----------
Chocolate, Orange, Strawberry    

[Java,1]
----------
Chocolate, Java, Orange, Strawberry

Simplification:

Actually, I used the CTE as the target because your original example had the WHEN NOT MATCHED BY SOURCE THEN DELETE and nine times out of ten, when synchonizing a set, your target needs to be constrained by a subquery (a CTE in the case of MERGE).

However, your actual needs do not seem to require a WHEN NOT MATCHED BY SOURCE clause, and therefore you can simplify the above statement, with the same results:

;MERGE INTO IceCream AS TGT
USING (VALUES(@Flavor, @Exist)) AS SRC(Flavor, Exist)
   ON TGT.Flavor = SRC.Flavor
WHEN MATCHED AND SRC.Exist = 0
     THEN DELETE
WHEN NOT MATCHED BY TARGET AND SRC.Exist = 1
     THEN INSERT (Flavor) VALUES (SRC.Flavor);

Upvotes: 1

Related Questions