时间戳

首页 > 解决方案 > 将 Fetch First 与案例表达式一起使用

sql - 将 Fetch First 与案例表达式一起使用

问题描述

DB2 UDB v11.x。

是否可以在使用 CASE 的 UPDATE 查询中使用 FETCH FIRST？我有点理解为什么它不起作用，但想知道是否有办法。例如：

db2 "UPDATE mytable SET cstate = CASE WHEN cstate = 801 THEN cstate = 1 AND rstate = 1 WHEN cstate = 804 THEN cstate = 4 AND rstate = 4 END FETCH FIRST 20000 ROWS ONLY"

这导致一次只更改一行。

试过： 

db2 "UPDATE mytable SET cstate = CASE WHEN cstate = (SELECT cstate FROM mytable WHERE cstate = 801 FETCH FIRST 20000 ROWS ONLY) THEN cstate = 1 AND rstate = 1 WHEN cstate = (SELECT cstate FROM mytable WHERE cstate = 804 FETCH FIRST 20000 ROWS ONLY) THEN cstate = 4 AND rstate = 4 END"

这得到：

SQL0811N  The result of a scalar fullselect, SELECT INTO statement, or VALUES
INTO statement is more than one row

有什么办法可以做到这一点？希望不要将其拆分并重复表格两次（或一次必须进行一次更新）。

标签： sqldb2

解决方案

这将是最简单的方法

UPDATE
(   SELECT * FROM
    (   SELECT
            cstate
        ,   rstate
        ,   CASE WHEN cstate = 801 THEN 1 WHEN cstate = 804 THEN 4 ELSE cstate END new_cstate
        ,   CASE WHEN cstate = 801 THEN 1 WHEN cstate = 804 THEN 4 ELSE rstate END new_rstate
        FROM
            mytable
    )
    WHERE 
        cstate IS DISTINCT FROM new_cstate
    OR  rstate IS DISTINCT FROM new_rstate
    FETCH FIRST 20000 ROWS ONLY
)
SET cstate = new_cstate
,   rstate = new_rstate

继续运行它，直到它没有更新任何行。

推荐阅读