CODEWITHSUNDEEP
sql-servermergeupsert
user610217
user610217

Reputation:

MERGE with DELETE on target with partial match on source?

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?

Upvotes: 3

Views: 2686

Answers (1)

SqlZim
SqlZim

Reputation: 38063

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:

Upvotes: 6

Related Questions