CODEWITHSUNDEEP
sqlmergedb2
chris fowler
chris fowler

Reputation: 27

Merge Statement with conditions, IBM DB2 sql merge

I am curious if this would work. I'm currently locked out of the target table and am losing development time, so I can't test it for another week and want to move onto the next step.

The goal is to do as follows: Using 3 primary keys(cch_id, event_type, event_time) I need to compare two tables of data(source_tb and target_tb), then update the second table(Target_tb) based on the following 3 criteria: 1. For matching data, do not do anything 2. For records coming from target_tb but not coming from source_tb, expire the records aka set event_type = ‘EXPIRED’ 3. For records coming from source flow but not available in the target table, insert it.

MERGE target_tb as b USING source_tb as a ON (a.cch_id = b.cch_id, a.event_type = b.event_type, a.event_time = b.event_time) WHEN MATCHED THEN null//do nothing WHEN NOT MATCHED by b.cch_id THEN b.event_type = 'EXP' WHEN NOT MATCHED BY a.cch_id THEN INSERT a.*

Upvotes: 0

Views: 1609

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

You can't achieve this with MERGE because you need to update non-matching rows in target_tb.
If you still want to do this with a single statement, you may use select from data-change operation like below:

create table target_tb (key int not null, val int);
create table source_tb (key int not null, val int);
insert into target_tb values (1, 1), (2, 2);
insert into source_tb values (3, 3), (2, 2);

with 
  u as 
(
select key 
from old table
(
update target_tb t
set val = -1
where not exists (select 1 from source_tb s where s.key = t.key)
)
)
, i as
(
select key 
from new table
(
insert into target_tb
select key, val
from source_tb s
where not exists (select 1 from target_tb t where t.key = s.key)
)
)
select a
from (values 1) t(a);

select * from target_tb;

The result is:

|KEY        |VAL        |
|-----------|-----------|
|1          |-1         |
|2          |2          |
|3          |3          |

dbfiddle link.

Upvotes: 4

Charles
Charles

Reputation: 23783

I'd suggest using DB Fiddle.. https://dbfiddle.uk/?rdbms=db2_11.1

But no I don't believe your statement will work. Specifically the

  1. For records coming from target_tb but not coming from source_tb, expire the records aka set event_type = ‘EXPIRED’

Db2 merge doesn't support WHEN NOT MATCHED BY SOURCE clause that SQL Server supports...

Upvotes: 0

Related Questions