在Sql Server中等效于MySQL ON DUPLICATE KEY UPDATE

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);

谁能帮忙？

PS.我已经看到MERGE查询有类似的功能,但是我发现这个语法是非常不同的.

解决方法

我推荐这个：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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!