Menu
I have tried the following with a couple of variations but I continue to get errors. Any way to get this fixed. DB2 10.1 (DB2 for z/OS V10)
For the following
MERGE INTO TRGT t
USING SRC s
ON (t.ACCTID=s.ACCTID AND s.SEQID=123)
WHEN MATCHED THEN
UPDATE SET
MyFlag = 'Y'
Error: An unexpected token "SRC" was found following "". Expected tokens may include: "(". SQLSTATE=42601
SQLState: 42601 ErrorCode: -104
However for the following
MERGE INTO TRGT t
USING (SELECT SEQID, ACCTID FROM SRC WHERE SEQID=123) s
ON (t.ACCTID=s.ACCTID)
WHEN MATCHED THEN
UPDATE SET
MyFlag = 'Y'
Error: The use of the reserved word "SELECT" following "" is not valid. Expected tokens may include: "VALUES". SQLSTATE=42601
SQLState: 42601 ErrorCode: -199
722
The MERGE statement updates a target (a table or view, or the underlying tables or views of a fullselect) using data from a source (result of a table reference). Rows in the target that match the source can be deleted or updated as specified, and rows that do not exist in the target can be inserted.
The MERGE statement combines INSERT, UPDATE, and DELETE operations into a single statement, eliminating the need to write separate logic for each. It changes the data in a target table based on the data in a source table.
Multiple tables can be merged by columns in SQL using joins. Joins merge two tables based on the specified columns (generally, the primary key of one table and a foreign key of the other). Below is the generic syntax of SQL joins. USING (id);
Both the MERGE and UPDATE statements are designed to modify data in one table based on data from another, but MERGE can do much more. Whereas UPDATE can only modify column values you can use the MERGE statement to synchronize all data changes such as removal and addition of row.
Unfortunately (and I never understood why), in DB2 for z/OS, you can't use a table as a source for the MERGE, you can only use a VALUES clause. Additionally, if you want to merge multiple rows, you have to use host variable arrays, and specify the number of values are in your array.
MERGE INTO TRGT t
USING (VALUES (:param1, :param2) FOR :paramNumRows) s
ON (t.ACCTID=s.ACCTID)
WHEN MATCHED THEN
UPDATE SET MyFlag = 'Y'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
