DB2 update语句使用cte(with as)

借助DB2 Old-New-Final-Table中间结果表，可以实现update语句使用cte，甚至可以在一句SQL里完成增删改查。

SQL示例

-- update语句使用cte
WITH data(NN) AS (
    VALUES (1)
    UNION ALL
    SELECT NN + 1
    FROM data
    WHERE NN < 10
)
SELECT *
FROM NEW TABLE (
    UPDATE TEST.T_USER
    SET AGE = (SELECT MAX(NN) FROM data)
    WHERE USER_ID = 'honey_pie'
    );

-- 一句SQL里增删改查
WITH data(USER_ID) AS (
    SELECT 'honey_pie'
    FROM SYSIBM.DUAL
),
     do_insert AS (
         SELECT *
         FROM NEW TABLE (
             INSERT INTO TEST.T_USER(USER_ID)
             SELECT USER_ID
             FROM data
             )
     ),
     do_update AS (
         SELECT *
         FROM NEW TABLE (
             UPDATE TEST.T_USER
             SET USER_NAME = 'hypocrite'
             WHERE USER_ID = (SELECT USER_ID FROM data)
             )
     ),
     do_delete AS (
         SELECT *
         FROM OLD TABLE (
             DELETE
             FROM TEST.T_USER
             WHERE USER_ID = (SELECT USER_ID FROM data)
             )
     )
SELECT *
from do_insert
union all
select *
from do_update
union all
select *
from do_delete;