假设MyTable的表结构(KEY, datafield1, datafield2…)
通常,我希望更新现有记录,或者插入不存在的新记录。
从本质上讲:
IF (key exists)
run update command
ELSE
run insert command
最好的写法是什么?
假设MyTable的表结构(KEY, datafield1, datafield2…)
通常,我希望更新现有记录,或者插入不存在的新记录。
从本质上讲:
IF (key exists)
run update command
ELSE
run insert command
最好的写法是什么?
当前回答
您可以使用这个查询。适用于所有SQL Server版本。它简单明了。但是您需要使用两个查询。如果不能使用MERGE,可以使用
BEGIN TRAN
UPDATE table
SET Id = @ID, Description = @Description
WHERE Id = @Id
INSERT INTO table(Id, Description)
SELECT @Id, @Description
WHERE NOT EXISTS (SELECT NULL FROM table WHERE Id = @Id)
COMMIT TRAN
注意:请解释否定答案
其他回答
我尝试了下面的解决方案,它为我工作,当插入语句发生并发请求时。
begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
update table set ...
where key = @key
end
else
begin
insert table (key, ...)
values (@key, ...)
end
commit tran
您可以使用这个查询。适用于所有SQL Server版本。它简单明了。但是您需要使用两个查询。如果不能使用MERGE,可以使用
BEGIN TRAN
UPDATE table
SET Id = @ID, Description = @Description
WHERE Id = @Id
INSERT INTO table(Id, Description)
SELECT @Id, @Description
WHERE NOT EXISTS (SELECT NULL FROM table WHERE Id = @Id)
COMMIT TRAN
注意:请解释否定答案
如果执行UPDATE If -no-rows-updated then INSERT路由,考虑先执行INSERT以防止竞争条件(假设没有插入DELETE)
INSERT INTO MyTable (Key, FieldA)
SELECT @Key, @FieldA
WHERE NOT EXISTS
(
SELECT *
FROM MyTable
WHERE Key = @Key
)
IF @@ROWCOUNT = 0
BEGIN
UPDATE MyTable
SET FieldA=@FieldA
WHERE Key=@Key
IF @@ROWCOUNT = 0
... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
END
除了避免竞争条件之外,如果在大多数情况下记录已经存在,那么这将导致INSERT失败,浪费CPU。
在SQL2008以后使用MERGE可能更可取。
如果您首先尝试更新,然后插入,那么竞态条件真的重要吗? 假设你有两个线程想要为key key设置一个值:
线程1:value = 1 线程2:value = 2
竞态条件场景示例
键没有定义 线程1更新失败 线程2更新失败 线程1或线程2中的一个成功插入。例如线程1 另一个线程插入失败(错误重复键)-线程2。 结果:要插入的两个步骤中的“第一个”决定值。 期望结果:最后一个线程写入数据(更新或插入)应该决定值
但;在多线程环境中,操作系统调度器决定线程执行的顺序——在上面的场景中,我们有这个竞争条件,是操作系统决定执行的顺序。从系统的观点来看,说“线程1”或“线程2”是“第一个”是错误的。
当线程1和线程2的执行时间非常接近时,竞态条件的结果并不重要。唯一的要求应该是其中一个线程应该定义结果值。
对于实现:如果更新后插入导致错误的“重复键”,这应该被视为成功。
此外,我们当然不应该假定数据库中的值与您最后写入的值相同。
请看我对之前一个非常相似的问题的详细回答
@Beau Crawford's在SQL 2005及以下是一个很好的方法,尽管如果你授予rep它应该去第一个人SO它。唯一的问题是对于插入,它仍然是两个IO操作。
MS Sql2008引入了SQL:2003标准的合并:
merge tablename with(HOLDLOCK) as target
using (values ('new value', 'different value'))
as source (field1, field2)
on target.idfield = 7
when matched then
update
set field1 = source.field1,
field2 = source.field2,
...
when not matched then
insert ( idfield, field1, field2, ... )
values ( 7, source.field1, source.field2, ... )
现在它真的只是一个IO操作,但糟糕的代码:-(