Unanswered: need syntax for MERGE statement on the Z
I've used the MERGE statement before in Oracle, but I'm having trouble getting in work for db2 9.1 on the Z. I know it's different, but I cannot find concrete examples of how it's different. Here is a very simple test:
CREATE TABLE TEMP_TEST1 (ID INTEGER, DATA VARCHAR(20) );
CREATE TABLE TEMP_TEST2 (ID INTEGER, DATA VARCHAR(20) );
INSERT INTO TEMP_TEST1 VALUES (2,'SOMEDATA_B1');
INSERT INTO TEMP_TEST1 VALUES (3,'SOMEDATA_C1');
INSERT INTO TEMP_TEST2 VALUES (1,'SOMEDATA_A2');
INSERT INTO TEMP_TEST2 VALUES (3,'SOMEDATA_C2');
INSERT INTO TEMP_TEST2 VALUES (4,'SOMEDATA_D2');
MERGE INTO TEMP_TEST1 AS T1
USING TEMP_TEST2 AS T2
ON T1.ID = T2.ID
WHEN MATCHED THEN
UPDATE SET T1.DATA = T2.DATA
WHEN NOT MATCHED THEN
INSERT VALUES (T2.ID, T2.DATA);
SQL0104N An unexpected token "TEMP_TEST2" was found following "". Expected tokens may include: "(".
Can someone explain why this is not working or explain the syntax for the Z?
I'm trying to use it as a MERGE statement to replace a cursor Insert/Update loop in a SQL Stored Procedure. Replacing 200,000 or more separate single Insert/Updates with a single statement is much more efficient.
It works just fine in Oracle or DB2 LUW, just not on the Z
I don't think I can use it in the SP. Unless someone has an example...