假设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
最好的写法是什么?
当前回答
我尝试了下面的解决方案,它为我工作,当插入语句发生并发请求时。
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
其他回答
做一个if存在…其他的……至少要做两个请求(一个检查,一个采取行动)。下面的方法在记录存在时只需要一个,如果需要插入则需要两个:
DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')
/*
CREATE TABLE ApplicationsDesSocietes (
id INT IDENTITY(0,1) NOT NULL,
applicationId INT NOT NULL,
societeId INT NOT NULL,
suppression BIT NULL,
CONSTRAINT PK_APPLICATIONSDESSOCIETES PRIMARY KEY (id)
)
GO
--*/
DECLARE @applicationId INT = 81, @societeId INT = 43, @suppression BIT = 0
MERGE dbo.ApplicationsDesSocietes WITH (HOLDLOCK) AS target
--set the SOURCE table one row
USING (VALUES (@applicationId, @societeId, @suppression))
AS source (applicationId, societeId, suppression)
--here goes the ON join condition
ON target.applicationId = source.applicationId and target.societeId = source.societeId
WHEN MATCHED THEN
UPDATE
--place your list of SET here
SET target.suppression = source.suppression
WHEN NOT MATCHED THEN
--insert a new line with the SOURCE table one row
INSERT (applicationId, societeId, suppression)
VALUES (source.applicationId, source.societeId, source.suppression);
GO
用您需要的任何东西替换表和字段名。 注意使用状态。 然后在DECLARE行中为变量设置适当的值(和类型)。
欢呼。
不要忘记事务。性能很好,但是简单的方法(IF EXISTS..)非常危险。 当多个线程将尝试执行插入或更新时,您可以轻松地 获取主键冲突。
由@Beau Crawford和@Esteban提供的解决方案显示了大致的想法,但容易出错。
为了避免死锁和PK违规,你可以使用如下方法:
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
or
begin tran
update table with (serializable) set ...
where key = @key
if @@rowcount = 0
begin
insert into table (key, ...) values (@key,..)
end
commit tran
如果您首先尝试更新,然后插入,那么竞态条件真的重要吗? 假设你有两个线程想要为key key设置一个值:
线程1:value = 1 线程2:value = 2
竞态条件场景示例
键没有定义 线程1更新失败 线程2更新失败 线程1或线程2中的一个成功插入。例如线程1 另一个线程插入失败(错误重复键)-线程2。 结果:要插入的两个步骤中的“第一个”决定值。 期望结果:最后一个线程写入数据(更新或插入)应该决定值
但;在多线程环境中,操作系统调度器决定线程执行的顺序——在上面的场景中,我们有这个竞争条件,是操作系统决定执行的顺序。从系统的观点来看,说“线程1”或“线程2”是“第一个”是错误的。
当线程1和线程2的执行时间非常接近时,竞态条件的结果并不重要。唯一的要求应该是其中一个线程应该定义结果值。
对于实现:如果更新后插入导致错误的“重复键”,这应该被视为成功。
此外,我们当然不应该假定数据库中的值与您最后写入的值相同。
你可以使用:
INSERT INTO tableName (...) VALUES (...)
ON DUPLICATE KEY
UPDATE ...
使用这个,如果已经有一个特定键的条目,那么它将更新,否则,它将插入。