sql-server - MERGE 与 DELETE 在目标上与源上的部分匹配？

Question

假设我有一个包含复合主键的表:

create table FooBar (
   fooId int not null,
   barId int not null,
   status varchar(20) not null,
   lastModified datetime not null, 
   constraint PK_FooBar primary key (fooId, barId)
);

现在我有一些特定 fooId 的表格数据，可能是这样的:

1, 1, 'ACTIVE'
1, 2, 'INACTIVE'

...我想创建一个 MERGE 语句，将此表格数据视为权威 fooId 1，删除 中的所有不匹配记录FooBar 用于 fooId 1，但所有记录的 fooId 都不是 1。

例如，假设 FooBar 表当前有以下数据:

1, 1, 'ACTIVE', ... (some date, not typing it out)
2, 1, 'ACTIVE', ...
1, 3, 'INACTIVE', ...
2, 2, 'INACTIVE'

我想对上面提到的两个数据集运行合并语句，FooBar 中的结果数据集应该如下所示:

1, 1, 'ACTIVE', ...
2, 1, 'ACTIVE', ...
1, 2, 'INACTIVE', ...
2, 2, 'INACTIVE', ...

我希望删除 1, 3, 'INACTIVE' 行，并使用新修改的内容更新 1, 1, 'ACTIVE' 行时间戳，以及要插入的 1, 2, 'INACTIVE' 行。我还希望 2 的 fooId 的记录保持不变。

这可以在一条语句中完成吗？如果是，怎么办？

Answer 1

您可以使用 common table expressions对于您的目标和源，在这些 ctes 中，您可以应用过滤器来定义要使用的行:

-- t = Target = Destination Table = Mirror from Source
-- s = Source = New Data source to merge into Target table 

declare @FooId int = 1;

;with t as (
  select fooId, barId, [status], lastModified 
  from dbo.FooBar f 
  where f.fooId = @FooId
  )
,  s as (
  select fooId, barId, [status], lastModified=sysutcdatetime() 
  from src 
  where src.fooId = @FooId
  )
merge into t with (holdlock) -- holdlock hint for race conditions
using s 
  on (s.FooId = t.FooId and s.barId = t.barId)

    /* If the records matches, update status and lastModified. */
    when matched 
      then update set t.[status] = s.[status], t.lastModified = s.lastModified

    /* If not matched in table, insert the record */
    when not matched by target
      then insert (fooId,  barId,   [status],  lastModified)
        values (s.fooId, s.barId, s.[status], s.lastModified)

    /* If not matched by source, delete the record*/
    when not matched by source
      then delete
 output $action, inserted.*, deleted.*;

rextester 演示:http://rextester.com/KRAI9699

返回:

+---------+-------+-------+----------+---------------------+-------+-------+----------+---------------------+
| $action | fooId | barId |  status  |    lastModified     | fooId | barId |  status  |    lastModified     |
+---------+-------+-------+----------+---------------------+-------+-------+----------+---------------------+
| INSERT  | 1     | 2     | INACTIVE | 2017-09-06 16:21:21 | NULL  | NULL  | NULL     | NULL                |
| UPDATE  | 1     | 1     | ACTIVE   | 2017-09-06 16:21:21 | 1     | 1     | ACTIVE   | 2017-09-06 16:21:21 |
| DELETE  | NULL  | NULL  | NULL     | NULL                | 1     | 3     | INACTIVE | 2017-09-06 16:21:21 |
+---------+-------+-------+----------+---------------------+-------+-------+----------+---------------------+

---

合并引用:

• Use Caution with SQL Server''s MERGE Statement - Aaron Bertrand
• UPSERT Race Condition With Merge - Dan Guzman
• An Interesting MERGE Bug - Paul White
• Can I optimize this merge statement - Aaron Bertrand
• If you are using indexed views and MERGE, please read this! - Aaron Bertrand
• The Case of the Blocking Merge Statement (LCK_M_RS_U locks) - Kendra Little
• Writing t-sql merge statements the right way - David Stein