Does DB2 have an "insert or update" statement?
Yes, DB2 has the MERGE statement, which will do an UPSERT (update or insert).
MERGE INTO target_table USING source_table ON match-condition{WHEN [NOT] MATCHED THEN [UPDATE SET ...|DELETE|INSERT VALUES ....|SIGNAL ...]}[ELSE IGNORE]See:
https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en
I found this thread because I really needed a one-liner for DB2 INSERT OR UPDATE.
The following syntax seems to work, without requiring a separate temp table.
It works by using VALUES() to create a table structure . The SELECT * seems surplus IMHO but without it I get syntax errors.
MERGE INTO mytable AS mt USING ( SELECT * FROM TABLE ( VALUES (123, 'text') )) AS vt(id, val) ON (mt.id = vt.id)WHEN MATCHED THEN UPDATE SET val = vt.valWHEN NOT MATCHED THEN INSERT (id, val) VALUES (vt.id, vt.val);if you have to insert more than one row, the VALUES part can be repeated without having to duplicate the rest.
VALUES (123, 'text'), (456, 'more')The result is a single statement that can INSERT OR UPDATE one or many rows presumably as an atomic operation.
This response is to hopefully fully answer the query MrSimpleMind had in use-update-and-insert-in-same-query and to provide a working simple example of the DB2 MERGE statement with a scenario of inserting AND updating in one go (record with ID 2 is updated and record ID 3 inserted).
CREATE TABLE STAGE.TEST_TAB ( ID INTEGER, DATE DATE, STATUS VARCHAR(10) );COMMIT;INSERT INTO TEST_TAB VALUES (1, '2013-04-14', NULL), (2, '2013-04-15', NULL); COMMIT;MERGE INTO TEST_TAB T USING ( SELECT 3 NEW_ID, CURRENT_DATE NEW_DATE, 'NEW' NEW_STATUS FROM SYSIBM.DUALUNION ALL SELECT 2 NEW_ID, NULL NEW_DATE, 'OLD' NEW_STATUS FROM SYSIBM.DUAL ) AS S ON S.NEW_ID = T.ID WHEN MATCHED THEN UPDATE SET (T.STATUS) = (S.NEW_STATUS) WHEN NOT MATCHED THEN INSERT (T.ID, T.DATE, T.STATUS) VALUES (S.NEW_ID, S.NEW_DATE, S.NEW_STATUS);COMMIT;