How to run merge statement based on logic of joined table?

Question

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

Answer 1

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