Multiple queries in merge into statement

Question

I have a scenario where i want to insert record if it doesn't exist in DB2. If it already exists update is_active column to 0 of the existing row and insert the new row with is_active as 1.

I cannot use merge into as i cannot run 2 queries in when matched section.

How can i achieve this in batch.

If i were to run queries one by one i could have run them. But since there are millions of such records i want to do this in batch.

I want to do this using java prepared statement.

• mustaccio: Unfortunately, you didn't provide enough details about your table structure for a proper answer, but one way to approach this is to insert records unconditionally, then set is_active for earlier records if they exist.
• Abhishek: How i would do it in batch and how would i know which one is earlier record
• mustaccio: Exactly. How do you know "If it already exists"?

Answer 1

Using temporary table

create table test_tab (id int not null, is_active int not null) in userspace1;
with stream (id) as (values 
--1, 2, 3
--2, 3, 4
3, 4, 5
)
, upd as (
select count(1) cnt
from new table(
 update test_tab t
 set is_active=0
 where is_active=1 and exists (select 1 from stream s where s.id=t.id)
)
)
select count(1) cnt
from new table(
 insert into test_tab(id, is_active)
 select id, 1
 from stream
);
select * from test_tab;

Assuming you accumulate your batch values into the streams table before each attempt to change the base table test_tab.
You can do all your operations using such a single statement as described above.
If you want to use some real table instead of streams, it's worth to collect statistics on it before every such a statement and test the performance using various number of records in the streams table.

Using batch inserts

create table test_tab2 (id int not null, is_active int not null default 1, id_int bigint not null generated always as identity) in userspace1;
create index test_tab2_idx on test_tab2(id);
create trigger test_tab2_air
after insert on test_tab2
referencing new as n
for each row
update test_tab2 t
set is_active=0
where t.is_active=1 and t.id=n.id and t.id_int<>n.id_int;
insert into test_tab2 (id) values 
--1, 2, 3
--2, 3, 4
3, 4, 5
;

You can just insert new records in batches in your java program. The trigger deactivates the corresponding old records.

• Abhishek: Appologies, i wanted to this from java prepared statement. Sorry about missing this info. Have updated the question
• Lennart - Slava Ukraini: @Abhishek, nothing prevents you from using Marks suggestion in a prepared statement

Answer 2

I don't think you can do it in one statement with MERGE because you need to both update and insert when there is a match. But you can surely do it with 2 statements, UPDATE and INSERT (and a 3rd to clear the source), in a single transaction.

Lets say that we want to update table_name with the data from table new_values and the columns that identify a "match" are columns uq1 and uq2:

BEGIN TRANSACTION ;
 UPDATE table_name AS t -- the target
 SET is_active = 0
 WHERE EXISTS
 ( SELECT 1
 FROM new_values AS s -- the source
 WHERE s.uq1 = t.uq1
 AND s.uq2 = t.uq2
 -- AND <cond> -- batching condition
 )
 ;
 INSERT INTO table_name
 ( uq1, uq2, c3, c4, c5, is_active )
 SELECT 
 uq1, uq2, c3, c4, c5, 1
 FROM 
 new_values
 -- WHERE
 -- <cond> -- batching condition
 ;
 DELETE FROM new_values
 -- WHERE
 -- <cond> -- batching condition
 ; 
COMMIT ;

• Abhishek: How would i do it in batch, there millions of rows if it inserts one by one that takes so long. As you mentioned i could do it in 2 queries but how in batches
• ypercubeᵀᴹ: I made an edit with a way (assuming you have the data in a (temporary) source table)
• Abhishek: This is streaming data, scenario is 1 record arrives insert with is_active 1 now 2 record arrive which is update on the same key as 1 so need to update record 1 isactive to 0 and insert new record 2 with is_active 1, now i need to do this in batch of lets say 4000 so that at once 4000 recorda gets inserted and when these 4k records get insert it updates isactive to 0 if there is an update on the same key
• ypercubeᵀᴹ: Your previous comment said millions of rows. Now you say 1. Which is which? How many rows per second do you need to insert?
• Abhishek: Updated previous comment, there are 500 msg per sec