You are basically looking for an Insert or Update pattern sometimes referred to as an Upsert.
I recommend this: Insert or Update pattern for Sql Server – Sam Saffron
For a procedure that will be dealing with single rows, either these transactions would work well:
Sam Saffron’s First Solution (Adapted for this schema):
begin tran
if exists (
select *
from mytable with (updlock,serializable)
where col_a = @val_a
and col_b = @val_b
and col_c = @val_c
)
begin
update mytable
set col_d = @val_d
where col_a = @val_a
and col_b = @val_b
and col_c = @val_c;
end
else
begin
insert into mytable (col_a, col_b, col_c, col_d)
values (@val_a, @val_b, @val_c, @val_d);
end
commit tran
Sam Saffron’s Second Solution (Adapted for this schema):
begin tran
update mytable with (serializable)
set col_d = @val_d
where col_a = @val_a
and col_b = @val_b
and col_c = @val_c;
if @@rowcount = 0
begin
insert into mytable (col_a, col_b, col_c, col_d)
values (@val_a, @val_b, @val_c, @val_d);
end
commit tran
Even with a creative use of IGNORE_DUP_KEY, you’d still be stuck having to use an insert/update block or a merge statement.
update mytable
set col_d = 'val_d'
where col_a="val_a"
and col_b = 'val_b'
and col_c="val_c";
insert into mytable (col_a, col_b, col_c, col_d)
select 'val_a','val_b', 'val_c', 'val_d'
where not exists (select *
from mytable with (serializable)
where col_a="val_a"
and col_b = 'val_b'
and col_c="val_c"
);
The Merge answer provided by Spock should do what you want.
Merge isn’t necessarily recommended. I use it, but I’d never admit that to @AaronBertrand.