DB2-400 V7R3M0中CTE能否用于Merge/Update的表表达式？

First, let's cut to the chase: DB2 for i V7R3M0 does not support nesting CTEs inside the parentheses of the USING clause for MERGE statements—that's exactly why your commented-out code throws the SQL0199 error. However, there's a straightforward workaround to achieve the same logic using valid syntax for this version.

1. Fixing Your MERGE Statement #

Instead of nesting the CTE inside the USING subquery, define the CTE before the entire MERGE statement. This is supported in V7R3 and maintains the readability of using a CTE. Here's the corrected code:

WITH CTE3 AS (
 SELECT ID, QCLASS FROM SESSION.NEWVALUES
)
MERGE INTO SESSION.TESTTABLE AS TT
USING CTE3 AS NV
ON TT.ID = NV.ID
WHEN MATCHED AND TT.ID=2 THEN -- 仅更新ID为2的记录
UPDATE SET TT.PCLASS = NV.QCLASS ;

By moving the CTE to the top level, you create a temporary result set that the MERGE can directly reference in the USING clause, which aligns with V7R3's syntax rules.

2. Applying This to UPDATE Statements #

If you need to use a CTE with an UPDATE statement, the same top-level CTE approach works. Here are two valid patterns for V7R3:

Pattern 1: Correlated Subquery #

WITH CTE_UPD AS (
 SELECT ID, QCLASS FROM SESSION.NEWVALUES
)
UPDATE SESSION.TESTTABLE TT
SET PCLASS = (SELECT QCLASS FROM CTE_UPD CU WHERE CU.ID = TT.ID)
WHERE EXISTS (SELECT 1 FROM CTE_UPD CU WHERE CU.ID = TT.ID)
AND TT.ID = 2;

Pattern 2: JOIN in FROM Clause #

DB2 for i supports using a FROM clause in UPDATE statements, which pairs nicely with a top-level CTE:

WITH CTE_UPD AS (
 SELECT ID, QCLASS FROM SESSION.NEWVALUES
)
UPDATE SESSION.TESTTABLE TT
SET PCLASS = CU.QCLASS
FROM CTE_UPD CU
WHERE TT.ID = CU.ID
AND TT.ID = 2;

3. Why Your Original Code Failed #

The error SQL0199: Keyword AS not expected occurs because V7R3's SQL parser doesn't recognize the WITH keyword inside the USING subquery. The parser expects join-related keywords (like FULL, LEFT, CROSS) in that position, not a CTE definition. This restriction was relaxed in later versions of DB2 for i (e.g., V7R4 and beyond), but V7R3 requires the CTE to be declared at the statement level, not nested within a subquery.

内容的提问来源于stack exchange，提问作者spinjector