相当于 SQL Server 中的 MySQL ON DUPLICATE KEY UPDATE

Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server

提问by Nemo

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

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

Can anyone help?

任何人都可以帮忙吗？

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

附注。我读过那个MERGE查询有类似的功能，但我发现它的语法非常不同。

回答by SqlZim

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

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

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

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

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

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

---

---

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.

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

• A creative use of IGNORE_DUP_KEY - Paul White @Sql_Kiwi

• 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 Spockshould do what you want.

Spock 提供的 Merge 答案应该可以满足您的需求。

Mergeisn't necessarily recommended. I use it, but I'd never admit that to @AaronBertrand.

不一定推荐合并。我使用它，但我永远不会向@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 的 MERGE 语句 - Aaron Bertrand

• 我可以优化这个合并语句吗 - Aaron Bertrand

• 如果您正在使用索引视图和 MERGE，请阅读此内容！- 亚伦伯特兰

• 一个有趣的合并错误 - Paul White

• UPSERT 竞争条件与合并

回答by Spock

Try this... I've added comments to try and explain what happens where in a SQL Merge statement. Source : MSDN : Merge Statement

试试这个... 我已经添加了注释来尝试解释 SQL Merge 语句中发生的情况。来源：MSDN：合并声明

The Merge Statement is different to the ON DUPLICATE KEY UPDATE statement in that you can tell it what columns to use for the merge.

Merge 语句与 ON DUPLICATE KEY UPDATE 语句的不同之处在于您可以告诉它用于合并的列。

CREATE TABLE #mytable(COL_A VARCHAR(10), COL_B VARCHAR(10), COL_C VARCHAR(10), COL_D VARCHAR(10))
INSERT INTO #mytable VALUES('1','0.1', '0.2', '0.3'); --<These are the values we'll be updating

SELECT * FROM #mytable --< Starting values (1 row)

    MERGE #mytable AS target --< This is the target we want to merge into
    USING ( --< This is the source of your merge. Can me any select statement
        SELECT '1' AS VAL_A,'1.1' AS VAL_B, '1.2' AS VAL_C, '1.3' AS VAL_D --<These are the values we'll use for the update. (Assuming column COL_A = '1' = Primary Key)
        UNION
        SELECT '2' AS VAL_A,'2.1' AS VAL_B, '2.2' AS VAL_C, '2.3' AS VAL_D) --<These values will be inserted (cause no COL_A = '2' exists)
        AS source (VAL_A, VAL_B, VAL_C, VAL_D) --< Column Names of our virtual "Source" table
    ON (target.COL_A = source.VAL_A) --< This is what we'll use to find a match "JOIN source on Target" using the Primary Key
    WHEN MATCHED THEN --< This is what we'll do WHEN we find a match, in your example, UPDATE COL_D = VALUES(COL_D);
        UPDATE SET
            target.COL_B = source.VAL_B,
            target.COL_C = source.VAL_C,
            target.COL_D = source.VAL_D
    WHEN NOT MATCHED THEN --< This is what we'll do when we didn't find a match
    INSERT (COL_A, COL_B, COL_C, COL_D)
    VALUES (source.VAL_A, source.VAL_B, source.VAL_C, source.VAL_D)
    --OUTPUT deleted.*, $action, inserted.* --< Uncomment this if you want a summary of what was inserted on updated.
    --INTO #Output  --< Uncomment this if you want the results to be stored in another table. NOTE* The table must exists
    ;
SELECT * FROM #mytable --< Ending values (2 row, 1 new, 1 updated)

Hope that helps

希望有帮助

回答by Artjoman

Stored Procedure will save the day.

存储过程将节省一天。

Here I assume that COL_A and COL_B are unique columns and are type of INT NB! Don't have sql-server instance ATM so cannot guarantee correctness of the syntax. UPDATE! Here is a link to SQLFIDDLE

这里我假设 COL_A 和 COL_B 是唯一的列并且是 INT NB 类型！没有sql-server实例ATM所以不能保证语法的正确性。更新！这是SQLFIDDLE的链接

 CREATE TABLE mytable
(
COL_A int UNIQUE,
COL_B int UNIQUE,
COL_C int,
COL_D int,
)

GO

INSERT INTO mytable (COL_A, COL_B, COL_C, COL_D)
VALUES (1,1,1,1),
(2,2,2,2),
(3,3,3,3),
(4,4,4,4);
GO

CREATE PROCEDURE updateDuplicate(@COL_A INT, @COL_B INT, @COL_C INT, @COL_D INT)
AS
BEGIN
    DECLARE @ret INT
    SELECT @ret = COUNT(*) 
    FROM mytable p 
    WHERE p.COL_A = @COL_A 
        AND p.COL_B = @COL_B

     IF (@ret = 0) 
        INSERT INTO mytable (COL_A, COL_B, COL_C, COL_D)
        VALUES ( @COL_A, @COL_B, @COL_C, @COL_D)

     IF (@ret > 0)
        UPDATE mytable SET COL_D = @COL_D WHERE col_A = @COL_A AND COL_B = @COL_B  
END;
GO

Then call this procedure with needed values instead of Update statement

然后使用所需的值而不是 Update 语句调用此过程

exec updateDuplicate 1, 1, 1, 2
GO
SELECT * from mytable
GO

回答by StuartLC

You can simulate a near identitical behaviour using an INSTEAD OF TRIGGER:

您可以使用 模拟几乎相同的行为INSTEAD OF TRIGGER：

CREATE TRIGGER tMyTable ON MyTable
INSTEAD OF INSERT
AS
    BEGIN
        SET NOCOUNT ON;

        SELECT i.COL_A, i.COL_B, i.COL_C, i.COL_D, 
            CASE WHEN mt.COL_D IS NULL THEN 0 ELSE 1 END AS KeyExists 
            INTO #tmpMyTable
            FROM INSERTED i
            LEFT JOIN MyTable mt
            ON i.COL_D = mt.COL_D;

        INSERT INTO MyTable(COL_A, COL_B, COL_C, COL_D)
            SELECT COL_A, COL_B, COL_C, COL_D
                FROM #tmpMyTable
                WHERE KeyExists = 0;

        UPDATE mt
            SET mt.COL_A = t.COL_A, mt.COL_B = t.COL_B, mt.COL_C = t.COL_C
            FROM MyTable mt 
                INNER JOIN #tmpMyTable t 
                ON mt.COL_D = t.COL_D AND t.KeyExists = 1;
    END;

SqlFiddle here

SqlFiddle在这里

How it works

这个怎么运作

• We first project a list of all rows being attempted to be inserted into the table into a #temp table, noting which of those ARE already in the underlying table via a LEFT OUTER JOINon the key column(s) COL_Dwhich detect the duplication criteria.
• We then need to repeat the actual work of an INSERTstatement, by inserting those rows which are not already in the table (because of the INSTEAD OF, we have removed the responsibility of insertion from the engine and need to do this ourselves).
• Finally, we update all non-key columns in the matched rows with the newly 'inserted' data.

• 我们首先将尝试插入表中的所有行的列表投影到 #temp 表中，通过检测重复标准LEFT OUTER JOIN的键列上的a来注意哪些 ARE 已经在基础表中COL_D。
• 然后我们需要INSERT通过插入那些不在表中的行来重复语句的实际工作（因为INSTEAD OF，我们已经从引擎中删除了插入的责任，需要自己做这件事）。
• 最后，我们用新插入的数据更新匹配行中的所有非键列。

Salient Points

要点

• It works under the covers, i.e. any insert into the table while the trigger is enabled will be subject to the trigger (e.g. Application ORM, other stored procedures etc). The caller will generally be UNAWARE that the INSTEAD OFtrigger is in place.
• There must be a key of sorts to detect the duplicate criterion (natural or surrogate). I've assumed COL_Din this case, but it could be a composite key. (Key but cannot be IDENTITYfor obvious reasons, since the client wouldn't be inserting an Identity)
• The trigger works for both single and multiple row INSERTS

• 它在幕后工作，即在启用触发器时对表的任何插入都将受触发器的约束（例如应用程序 ORM、其他存储过程等）。调用者通常不会意识到INSTEAD OF触发器已就位。
• 必须有一个键来检测重复的标准（自然或代理）。我假设COL_D在这种情况下，但它可能是一个复合键。（关键但不能IDENTITY出于明显的原因，因为客户端不会插入身份）
• 触发器适用于单行和多行 INSERTS

NB

NB

• The standard disclaimers with triggersapply, and more so with INSTEAD OFtriggers - as this can cause surprising changes in observable behaviour of Sql Server, such as this - even well intended INSTEAD OFtriggers can cause hours of wasted effort and frustration for developers and DBA's who are not aware of their presence on your table.
• This will affect ALL inserts into the table. Not just yours.

• 触发器的标准免责声明适用，触发器更是如此INSTEAD OF- 因为这可能导致 Sql Server 的可观察行为发生令人惊讶的变化，例如这样 - 即使是精心设计的INSTEAD OF触发器也会导致不知道的开发人员和 DBA 浪费数小时的精力和沮丧他们出现在你的桌子上。
• 这将影响到表中的所有插入。不只是你的。

回答by Suchit kumar

There's no DUPLICATE KEY UPDATEequivalent in sql server,but you can use merged and when matched of sql server to get this done ,have a look here: multiple operations using merge

DUPLICATE KEY UPDATE在 sql server 中没有等价物，但是您可以使用合并和匹配的 sql server 来完成此操作，请查看此处： 使用合并的多个操作