if condition, then upsert else do nothing-CodePudding

I am trying to upsert a value only if a condition is true, otherwise I dont want to do anything to the record. My code seems to generate primary key errors when I run it again (indicating the insert is firing, but the whole statement should be skipped)

Error on second run: Violation of PRIMARY KEY constraint 'PK__chicken__A2D9E564407C90CD'. Cannot insert duplicate key in object 'dbo.chicken'. The duplicate key value is (10118-78843).

Query:

IF((select status from chicken where [PrimaryKey]='10118-78843')!='paid')
update [eComm]..[chicken] set [Status]='Overdue' where [PrimaryKey]='10118-78843' 
IF @@ROWCOUNT=0 insert into [eComm]..[chicken]([PrimaryKey],[Status]) values('10118-78843','Paid');

create example table code:

create table chicken(
PrimaryKey nvarchar(50) not null,
Status nvarchar(10),
PRIMARY KEY (PrimaryKey)
)

Goal (this is invalid syntax):

IF((select status from chicken where [PrimaryKey]='10118-78843')!='paid')
{
update [eComm]..[chicken] set [Status]='Overdue' where [PrimaryKey]='10118-78843' 
IF @@ROWCOUNT=0 insert into [eComm]..[chicken]([PrimaryKey],[Status]) values('10118-78843','Paid');
}

else, do nothing

CodePudding user response：

The problem is that you are not taking into account what happens if there is an existing row but the status is paid.

You need to tweak the logic a bit:

declare @status varchar(20) = (
    select c.status
    from chicken c
    where c.[PrimaryKey] = '10118-78843'
);

if (@status != 'paid')
    update [chicken]
    set [Status] = 'Overdue'
    where [PrimaryKey] = '10118-78843';
else if (@status is null)
    insert into [chicken] ([PrimaryKey], [Status])
    values ('10118-78843', 'Paid');

Alternatively:

insert into [chicken] ([PrimaryKey], [Status])
select '10118-78843', 'Paid'
where not exists (select 1
    from chicken c
    where c.[PrimaryKey] = '10118-78843'
);

if (@@ROWCOUNT = 0)
    update [chicken]
    set [Status] = 'Overdue'
    where [PrimaryKey] = '10118-78843'
      and status != 'Paid';