我只在條件為真時才嘗試更新一個值，否則我不想對記錄做任何事情。當我再次運行它時，我的代碼似乎產生了主鍵錯誤（表明插入正在觸發，但應該跳過整個陳述句）
第二次運行時出錯：Violation of PRIMARY KEY constraint 'PK__chicken__A2D9E564407C90CD'. Cannot insert duplicate key in object 'dbo.chicken'. The duplicate key value is (10118-78843).
詢問：
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 table chicken(
PrimaryKey nvarchar(50) not null,
Status nvarchar(10),
PRIMARY KEY (PrimaryKey)
)
目標（這是無效的語法）：
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');
}
否則，什么都不做
uj5u.com熱心網友回復：
問題是您沒有考慮如果存在現有行但狀態為paid.
您需要稍微調整一下邏輯：
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');
或者：
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';
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/443780.html