How to run merge statement based on logic of joined table?
Marcee last edited by
When I try to run a query like the following:
MERGE INTO MYTABLE_A AS MERGE_TARGET USING ( SELECT MYTABLE_A.A, MYTABLE_B.B, MYTABLE_A.C, MYTABLE_D, MYTABLE_E FROM MYTABLE_A INNER JOIN MYTABLE_B ON MYTABLE_B.A = MYTABLE_A.A WHERE MYTABLE_A.LOGIC = 1 AND MYTABLE_B.LOGIC = 2 ) AS MERGE_USING ON MERGE_USING.C = MERGE_TARGET.C AND MERGE_TARGET.OTHERLOGIC = 1 AND MERGE_TARGET.A IN ( SELECT MYTABLE_A.A FROM MYTABLE_A INNER JOIN MYTABLE_B ON MYTABLE_B.A = MYTABLE_A.A WHERE MERGE_USING.B = MYTABLE_B.B ) WHEN MATCHED THEN UPDATE SET MERGE_TARGET.D = MERGE_USING.D, MERGE_TARGET.E = MERGE_USING.E
It gives me this error:
Message: [SQL0115] Comparison operator IN not valid. Cause . . . . . : Simple comparison operators other than equal and not equal cannot be used with a list of items. ANY, ALL, and SOME comparison operators must be followed by a fullselect, rather than an expression or a list of items. Subqueries cannot be specified in a JOIN condition or in a CASE expression. Recovery . . . : Change either the comparison or the operand. Try the request again.
DB2 version: V7R1
It looks like you're overthinking your merge statement.
The error is telling you that it's expecting an equality operator instead of an
IN. For example,
AND MERGE_TARGET.A IN (should be
AND MERGE_TARGET.A =. Then you need to replace the subquery with a column name.
Looking at the below section of code further, I see you're trying to find if merge_target (which is table_a) has a value within a subquery that returns tableA.A. Further the subquery joins TableA to TableB on this same column (A), where TableB is then also equal to the current row in MERGE_USING (TableB). The net effect is no different than
AND MERGE_TARGET.A = MERGE_USING.A
AND MERGE_TARGET.A IN ( SELECT MYTABLE_A.A FROM MYTABLE_A INNER JOIN MYTABLE_B ON MYTABLE_B.A = MYTABLE_A.A WHERE MERGE_USING.B = MYTABLE_B.B )
When thinking of a MERGE, just think of it as a SIMPLE JOIN statement. INTO is TABLE 1 of the JOIN, USING is TABLE 2 of the JOIN, and ON is what JOINS them. Now that you have your condition, what do you do when it matches or does not match.
You also have an unneeded joins inside of your USING clause. Table_A is already your target. If we remove the join and move the WHERE conditions to the the ON portion of the MERGE, we'll end up with something like this...
MERGE INTO MYTABLE_A USING MYTABLE_B /* Below is your JOIN/WHERE condition. JOIN source and target here on the columns that need to match */ ON MYTABLE_B.A = MYTABLE_A.A AND MYTABLE_B.C = MYTABLE_A.C AND MYTABLE_A.LOGIC = 1 AND MYTABLE_A.OTHERLOGIC = 1 AND MYTABLE_B.LOGIC = 2 WHEN MATCHED THEN UPDATE SET MYTABLE_A.D = MYTABLE_B.D, MYTABLE_A.E = MYTABLE_B.E