MERGE with DELETE on target with partial match on source?

Question

Let's say I have a table with a compound primary key:

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

Now I have some tabular data for a particular fooId, maybe something like this:

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

...and I want to make a MERGE statement that treats this tabular data as authoritative for fooId 1 only, removing any non-matching records in FooBar that are for fooId 1, but leaving all records with a fooId that is not 1 alone.

For example, let's say the FooBar table currently has this data:

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

I would want to run a merge statement with the two datasets mentioned above, and the resultant data set in FooBar should look like:

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

I would want the row 1, 3, 'INACTIVE' to be deleted, and the 1, 1, 'ACTIVE' row to be updated with the new modified timestamp, and the 1, 2, 'INACTIVE' row to be inserted. I would also like the records for fooId of 2 to be unmodified.

Can this be done in a single statement? If so, how?

Normally a merge can insert rows from source that do not exist in the target, update rows that exist in both (source and target) and delete what is not matched by source (also conditioning this to foodId = 1 could be done) but maybe you should provide sample data of the source, the target and expected results to get a better answer. Maybe [this thread](https://stackoverflow.com/questions/7331725/how-to-delete-from-source-using-merge-command-in-sql-server-2008) helps. — Rigerta, Sep 06 '17 at 16:04

SqlZim · Accepted Answer · 2017-09-06T16:31:44.097

You can use common table expressions for your target and source, and within those ctes you can apply filters to define the rows you want to work with:

-- 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 demo: http://rextester.com/KRAI9699

returns:

+---------+-------+-------+----------+---------------------+-------+-------+----------+---------------------+
| $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 |
+---------+-------+-------+----------+---------------------+-------+-------+----------+---------------------+

merge reference:

Won't this get rid of the records for `fooId` 2 in the target table? — Jeremy Holovacs, Sep 06 '17 at 16:25
@JeremyHolovacs No, it will not. -- Check out the demo. In the common table expression for `t`, the `where f.fooId = @FooId` limits it to rows where `fooId = 1`. Only those rows are part of the `merge` operations. — SqlZim, Sep 06 '17 at 16:26
...that is too cool for words. I had no idea CTE's could be used like that. — Jeremy Holovacs, Sep 06 '17 at 16:30

MERGE with DELETE on target with partial match on source?

1 Answers1