Merge by target clause not updating desired columns

Question

I need to write an SQL instruction (not a procedure) that updates a date column AND insert the line if an ID is already present in target.

Target table:

ID  DT_DEBVAL   DT_FINVAL
1   2021-09-01  2050-12-31
2   2021-09-01  2050-12-31

New incoming data:

ID  DT_DEBVAL   DT_FINVAL
2   2021-09-08  2050-12-31
3   2021-09-08  2050-12-31
4   2021-09-08  2050-12-31

Desired output:

ID  DT_DEBVAL   DT_FINVAL
1   2021-09-01  2050-12-31
2   2021-09-01  2021-09-07 -- current date minus 1 day 
2   2021-09-08  2050-12-31 
3   2021-09-08  2050-12-31
4   2021-09-08  2050-12-31

I did same thing as here. This is my version of code:

insert into t1 (id, DT_DEBVAL, DT_FINVAL) 
select  t.id, t.DT_DEBVAL, t.DT_FINVAL from ( 
merge t1 As Target 
    using (select * from t2) AS Source
    on Target.id=Source.id and Target.dt_finval=Source.dt_finval
    when matched then
        update set Target.DT_FINVAL=DATEADD(day, -1, getdate())
    when not matched by target then insert (id, dt_debval, dt_finval) values (Source.id, Source.dt_debval, Source.dt_finval) OUTPUT $ACTION as Act, Inserted.* ) t 
    where t.Act  = 'Update'
select * from t1;

And This is obtained output:

ID  DT_DEBVAL   DT_FINVAL
1   2021-09-01  2050-12-31
2   2021-09-01  2021-09-07
2   2021-09-01  2021-09-07
3   2021-09-08  2050-12-31
4   2021-09-08  2050-12-31

As you can see, lines with ID=2 are not entirely correct. Any idea about why is it reacting this way?

Some bits on MERGE (read [this post](https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-to-use-merge/) and [this post](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/) in full) and consider separate `update`/`insert` statements [like this](https://sqlperformance.com/2020/09/locking/upsert-anti-pattern). — Aaron Bertrand, Sep 08 '21 at 15:29

GarethD · Accepted Answer · 2021-09-08T16:29:19.967

Your issue is that when you are referencing the memory-resident table inserted, you are referencing the row that is being updated, and returning the newly updated values, so you are just inserting the same data again. You need to reference the values from the source, e.g.

OUTPUT $ACTION as Act, inserted.ID, inserted.DT_FINVAL AS DT_DEBVAL, source.DT_FINVAL

So your full query would be:

insert into t1 (ID, DT_DEBVAL, DT_FINVAL) 
select  t.ID, GETDATE(), t.DT_FINVAL from ( 
merge t1 As Target 
    using (select * from t2) AS Source
    on Target.ID=Source.ID and Target.DT_FINVAL=Source.DT_FINVAL
    when matched then
        update set Target.DT_FINVAL=DATEADD(day, -1, GETDATE())
    when not matched by target then 
       insert (ID, DT_DEBVAL, DT_FINVAL) 
       values (Source.ID, Source.DT_DEBVAL, Source.DT_FINVAL) 
      OUTPUT $ACTION as Act, inserted.id, source.DT_FINVAL) t 
where t.Act  = 'Update';
    
    
select * from t1 order by ID;

Example on SQL Fiddle

Potential alternative:

insert into t1 (ID, DT_DEBVAL, DT_FINVAL) 
select  t.ID, t.DT_DEBVAL, t.DT_FINVAL from ( 
merge t1 As Target 
    using (select * from t2) AS Source
    on Target.ID=Source.ID and Target.DT_FINVAL=Source.DT_FINVAL
    when matched then
        update set Target.DT_FINVAL=DATEADD(day, -1, Source.DT_DEBVAL)
    when not matched by target then 
       insert (ID, DT_DEBVAL, DT_FINVAL) 
       values (Source.ID, Source.DT_DEBVAL, Source.DT_FINVAL) 
      OUTPUT $ACTION as Act, inserted.id, Source.DT_DEBVAL, source.DT_FINVAL) t 
where t.Act  = 'Update';

What I would say on all of this though, is that your solution is probably over complicated, and while I don't vehemently stay away from merge (I will avoid in most cases, but am as yet to find a suitable alternative to capturing columns in the output that are not part of the insert), this is one where I definitely wouldn't bother with it, because it offers no advantage.

Your logic is always:

Change the end date of any existing "Active" records
Insert all values from incoming data if they don't already exist

Which would require the following query:

BEGIN TRANSACTION;

UPDATE t1 WITH (UPDLOCK, SERIALIZABLE)
SET    DT_FINVAL = DATEADD(DAY, -1, t2.DT_DEBVAL)
FROM   t1
       INNER JOIN t2
           ON t2.ID = t1.ID
           AND t2.DT_FINVAL = t1.DT_FINVAL;

INSERT t1 (ID, DT_DEBVAL, DT_FINVAL)
SELECT t2.ID, t2.DT_DEBVAL, t2.DT_FINVAL
FROM   t2
WHERE  NOT EXISTS (SELECT 1 FROM t1 WHERE t1.ID = t2.ID AND t2.DT_FINVAL = t1.DT_FINVAL);

COMMIT TRANSACTION;

I think aside from avoiding merge and its bugs, this makes the intent much more clear (to me at least, and I'd consider myself very familiar with the MERGE syntax). It also reduces the total number of operations, you are now just doing an update and an insert, rather than a merge (insert & update) followed by a further insert.

Thank you. Just a little correction: I only want column DT_FINVAL of old data line to get updated. Your code also updates column DT_DEBVAL of new data. I slightly changed from this: `OUTPUT $ACTION as Act, inserted.ID, inserted.DT_FINVAL AS DT_DEBVAL, source.DT_FINVAL` to `OUTPUT $ACTION as Act, inserted.ID, source.DT_DEBVAL, source.DT_FINVAL` And it works perfectly. Thanks again — Haha, Sep 08 '21 at 16:08
Ah, I originally had that, but realised I had removed the logic about `GETDATE()` and was using the `DT_DEBVAL` from the source. I've corrected the main solution to insert `GETDATE()` as the new `DT_DEBVAL` (to ensure consistency with the updated `DT_FINVAL`. I've also posted an alternative, that I think may make more sense, where the value set for `DT_FINVAL` is based on the incoming value for `DT_DEBVAL`, just in case this is ever different than the current date. — GarethD, Sep 08 '21 at 16:15

Merge by target clause not updating desired columns

1 Answers1