我试图在sql Server(2012)中找到相当于以下
MySql查询？
INSERT INTO mytable (COL_A,COL_B,COL_C,COL_D) VALUES ( 'VAL_A','VAL_B','VAL_C','VAL_D') ON DUPLICATE KEY UPDATE COL_D= VALUES(COL_D);
谁能帮忙？
解决方法
您基本上正在寻找一个插入或更新模式,有时称为Upsert.
我推荐这个：Insert or Update pattern for Sql Server – Sam Saffron
对于将处理单行的过程,这些事务可以很好地运行：
Sam Saffron的第一个解决方案(适用于此架构)：
begin tran
if exists (
select *
from mytable with (updlock,serializable)
where col_a = @val_a
and col_b = @val_b
and col_c = @val_c
)
begin
update mytable
set col_d = @val_d
where col_a = @val_a
and col_b = @val_b
and col_c = @val_c;
end
else
begin
insert into mytable (col_a,col_b,col_c,col_d)
values (@val_a,@val_b,@val_c,@val_d);
end
commit tran
Sam Saffron的第二个解决方案(适用于此架构)：
begin tran
update mytable with (serializable)
set col_d = @val_d
where col_a = @val_a
and col_b = @val_b
and col_c = @val_c;
if @@rowcount = 0
begin
insert into mytable (col_a,col_d)
values (@val_a,@val_d);
end
commit tran
即使使用了IGNORE_DUP_KEY,您仍然必须使用插入/更新块或合并语句.
> A creative use of IGNORE_DUP_KEY – Paul White @Sql_Kiwi
update mytable
set col_d = 'val_d'
where col_a = 'val_a'
and col_b = 'val_b'
and col_c = 'val_c';
insert into mytable (col_a,col_d)
select 'val_a','val_b','val_c','val_d'
where not exists (select *
from mytable with (serializable)
where col_a = 'val_a'
and col_b = 'val_b'
and col_c = 'val_c'
);
The Merge answer provided by Spock应该做你想要的
合并不一定是推荐的.我使用它,但我永远不会承认@AaronBertrand.
> Use Caution with SQL Server’s MERGE Statement – Aaron Bertrand
> Can I optimize this merge statement – Aaron Bertrand
> If you are using indexed views and MERGE,please read this! – Aaron Bertrand
> An Interesting MERGE Bug – Paul White
> UPSERT Race Condition With Merge
