This assumes FOO has three columns, KEY_, COL1_ and COL2_....
Create the test table:
declare global temporary table FOO as (
select * from(values('1','xxxxxx','yyyyyy')) x(KEY_, COL1_, COL2_)
) with data with replace
;;
Merge new data in:
merge into FOO
using (values('1','x','y')) x(KEY_, VAL1_, VAL2_)
on (x.KEY_ = foo.KEY_)
when matched then
update set (COL1_, COL2_) = (x.VAL1_, x.VAL2_)
when not matched then
insert (KEY_, COL1_, COL2_) values(x.KEY_, x.VAL1_, x.VAL2_)
;;
Tim.
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Patrik Schindler <poc@xxxxxxxxxx>
Sent: 20 August 2021 10:46
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL: DB2 MERGE INTO vs. MySQL REPLACE INTO
Hello,
I struggle to get the concept of MERGE INTO into my head. I'm used about the MySQL REPLACE INTO syntax, though.
REPLACE INTO foo VALUES ('val1', 'val2', 'val3');
This is basically an implicit "if" regarding the primary key field in a table:
- If a record with the primary key value already exists, it is updated with the values given in (),
- if there isn't a record with that primary key value, it's a plain insert.
Can I achieve the same with MERGE INTO? If yes, how?
The examples I found on the net are very elaborated and complex. Thus a very easy example would be more helpful.
Thank you!
:wq! PoC
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
