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

i wish use single sql statement (i.e., "merge") manage table representing mathematical set: insert element if true, else remove.

using ice cream example, want add flavor or remove flavor list based on @exist flag below. possible? i've played several hours , either syntatically correct , doesn't insert row or write query think needs done won't compile because syntax isn't valid. (msdn merge "man" page)

here 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 t using icecream s on s.flavor=t.flavor ,     s.flavor=@flavor ,     t.flavor=@flavor when not matched target , @exist=1     insert (flavor) values (@flavor) when not matched source , @exist=0     delete output $action, inserted.*, deleted.*; 

although there different ways of accomplishing goal, merge statement work:

declare @flavor varchar(50), @exist bit;  set @flavor = 'orange'; set @exist  = 1;  ;with tgt (select flavor icecream flavor = @flavor) merge tgt using (values(@flavor, @exist)) src(flavor, exist)    on tgt.flavor = src.flavor when matched , src.exist = 0      delete when not matched target , src.exist = 1      insert (flavor) values (src.flavor); 

start initial list:

insert icecream values ('vanilla'), ('chocolate'), ('strawberry')   

and testing 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, used cte target because original example had when not matched source delete , 9 times out of ten, when synchonizing set, target needs constrained subquery (a cte in case of merge).

however, actual needs not seem require when not matched source clause, , therefore can simplify above statement, same results:

;merge icecream tgt using (values(@flavor, @exist)) src(flavor, exist)    on tgt.flavor = src.flavor when matched , src.exist = 0      delete when not matched target , src.exist = 1      insert (flavor) values (src.flavor);