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 stateme