Sample Header Ad - 728x90

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

0 votes
1 answer
188 views
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
Asked by Sarov (281 rep)
Feb 18, 2022, 04:56 PM
Last activity: Feb 22, 2022, 12:07 AM