等效于SQL Server中的MySQL ON DUPLICATE KEY UPDATE [英] Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server

问题描述

我正在尝试在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);

任何人都可以帮忙吗?

PS.我已经阅读到MERGE查询具有相似的功能，但是我发现该语法非常不同.

解决方案

您基本上是在寻找插入或更新模式，有时也称为 Upsert.

我建议这样做:为Sql Server插入或更新模式-山姆藏红花

对于将要处理单行的过程，这些事务都将运行良好:

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_b, col_c, col_d)
          values (@val_a, @val_b, @val_c, @val_d);
     end
commit tran
 

---

即使创造性地使用 IGNORE_DUP_KEY ，您仍然不得不使用插入/更新块或合并语句.

• 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_b, col_c, 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' );

  ---

  Spock提供的合并答案应该可以满足您的要求.

  不一定建议

  合并.我使用它，但是我永远不会接受@AaronBertrand.

  • 将警告与SQL Server的MERGE语句-Aaron Bertrand

  • 我是否可以优化此合并语句-Aaron贝特朗(Bertrand)

  • 如果您使用索引视图和MERGE ， 请阅读这个！ -亚伦·伯特兰(Aaron Bertrand)

  • 有趣的合并错误-保罗·怀特(Paul White)

  • 具有合并功能的UPSERT竞赛条件

  I am trying to find an equivalent of the following MySql query in Sql Server (2012)?

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

  Can anyone help?

  PS. I have read that MERGE query has similar function, but I find the syntax of that very different.

  解决方案

  You are basically looking for an Insert or Update pattern sometimes referred to as an Upsert.

  I recommend this: Insert or Update pattern for Sql Server - Sam Saffron

  For a procedure that will be dealing with single rows, either these transactions would work well:

  Sam Saffron's First Solution (Adapted for this schema):

  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's Second Solution (Adapted for this schema):

  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_b, col_c, col_d)
            values (@val_a, @val_b, @val_c, @val_d);
       end
  commit tran
  

  ---

  Even with a creative use of IGNORE_DUP_KEY, you'd still be stuck having to use an insert/update block or a merge statement.

  • 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_b, col_c, 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 should do what you want.

  Merge isn't necessarily recommended. I use it, but I'd never admit that to @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

  这篇关于等效于SQL Server中的MySQL ON DUPLICATE KEY UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！