Query
如何根據聯接表的邏輯執行合併語句?
當我嘗試執行如下查詢時:
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
它給了我這個錯誤:
資訊:
$$ SQL0115 $$比較運算符 IN 無效。原因 。. . . . : 除了等於和不等於之外的簡單比較運算符不能與項目列表一起使用。ANY、ALL 和 SOME 比較運算符後面必須跟一個全查詢,而不是表達式或項目列表。不能在 JOIN 條件或 CASE 表達式中指定子查詢。恢復 。. . :更改比較或操作數。再次嘗試請求。
DB2 版本:V7R1
看起來你在考慮你的合併語句。
該錯誤告訴您它需要一個相等運算符而不是
IN
. 例如,AND MERGE_TARGET.A IN (
應該是AND MERGE_TARGET.A =
。然後您需要將子查詢替換為列名。進一步查看下面的程式碼部分,我看到您正在嘗試查找 merge_target(即 table_a)是否在返回 tableA.A 的子查詢中具有值。此外,子查詢在同一列 (A) 上將 TableA 連接到 TableB,其中 TableB 也等於 MERGE_USING (TableB) 中的目前行。淨效應與
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 )
在考慮 MERGE 時,只需將其視為 SIMPLE JOIN 語句。INTO 是 JOIN 的 TABLE 1,USING 是 JOIN 的 TABLE 2,ON 是 JOIN 的內容。現在你有了你的條件,當它匹配或不匹配時你會做什麼。
您的 USING 子句中還有一個不需要的連接。Table_A 已經是您的目標。如果我們刪除連接並將 WHERE 條件移動到 MERGE 的 ON 部分,我們將得到類似這樣的結果……
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