CODEWITHSUNDEEP
sqlsql-servermerge
Haha
Haha

Reputation: 1009

Merge by target clause not updating desired columns

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?

Upvotes: 0

Views: 116

Answers (1)

GarethD
GarethD

Reputation: 69759

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.

Upvotes: 1

Related Questions