Is it possible to get a result if I combine the WITH clause and MERGE statement directly into one SELECT statement? Combining WITH clause with INSERT, UPDATE and DELETE works but MERGE does not work with “WITH clause” in DB2.
Here are fiddle link for INSERT, UPDATE and DELETE.
and fiddle link for MERGE
db2 code:
CREATE TABLE target_table (id, name, value) AS
(
VALUES
(1, 'item1', 1)
, (2, 'item1', 1)
, (3, 'item1', 1)
, (4, 'item2', 1)
, (5, 'item3', 1)
, (6, 'item3', 1)
, (7, 'item3', 9)
, (8, 'item3', 9)
, (9, 'item3', 9)
) WITH DATA
CREATE TABLE source_table (id, name, value) AS
(
VALUES
(1, 'item1', 2)
) WITH DATA
with
cte_1_insert as (
SELECT count (1) as DUMMY_1 from NEW TABLE
(
MERGE INTO target_table AS tgt
USING source_table AS src
ON tgt.id = src.id
WHEN MATCHED THEN
UPDATE SET tgt.name = src.name, tgt.value = src.value
WHEN NOT MATCHED THEN
INSERT (id, name, value) VALUES (src.id, src.name, src.value)
))
select * from target_table order by ID ASC;
expected result:
------------------------------------|
|ID |NAME |VALUE |
|1 |item1 |2 |
|2 |item1 |1 |
|3 |item1 |1 |
|4 |item2 |1 |
|5 |item3 |1 |
|6 |item3 |1 |
|7 |item3 |9 |
|8 |item3 |9 |
|9 |item3 |9 |
------------------------------------|