SQL Server 上的 INSERT OR UPDATE 解决方案
假设表结构为MyTable(KEY, datafield1, datafield2...).
我经常想更新现有记录，或者插入一条不存在的新记录.
Often I want to either update an existing record, or insert a new record if it doesn't exist.
基本上:
IF (key exists)
run update command
ELSE
run insert command
写这个的最佳表现方式是什么?
What's the best performing way to write this?
推荐答案
不要忘记事务.性能很好，但简单的(如果存在..)方法非常危险.
当多个线程将尝试执行插入或更新时，您可以轻松获取主键违规.
don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.
When multiple threads will try to perform Insert-or-update you can easily
get primary key violation.
@Beau Crawford & 提供的解决方案@Esteban 展示总体思路但容易出错.
Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.
为避免死锁和 PK 违规，您可以使用以下内容:
To avoid deadlocks and PK violations you can use something like this:
begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
update table set ...
where key = @key
end
else
begin
insert into table (key, ...)
values (@key, ...)
end
commit tran
或
begin tran
update table with (serializable) set ...
where key = @key
if @@rowcount = 0
begin
insert into table (key, ...) values (@key,..)
end
commit tran
相关文章