需要使用ORDER BY“绑定”UPDATE.我正在尝试使用游标,但得到错误：

06000

码：

BEGIN;
    DECLARE cursUpd CURSOR FOR SELECT * FROM "table" WHERE "field" = 5760 AND "sequence" >= 0 AND "sequence" < 9 ORDER BY "sequence" DESC;
    UPDATE "table" SET "sequence" = "sequence" + 2 WHERE CURRENT OF cursUpd;
    CLOSE cursUpd;
COMMIT;

怎么做得对？

更新1

没有光标,当我这样做时：

UPDATE "CableLinePoint" AS "t"
SET "sequence" = t."sequence" + 2
from (
    select max("sequence") "sequence","id"
    from "CableLinePoint"
    where
        "CableLine" = 5760
    group by "id"
    ORDER BY "sequence" DESC
) "s"
where "t"."id" = "s"."id" and "t"."sequence" = "s"."sequence"

我得到了唯一的错误.因此,需要从最终而不是从头开始更新.

更新2

表：

id|CableLine|sequence
10|    2    |    1
11|    2    |    2
12|    2    |    3
13|    2    |    4
14|    2    |    5

需要更新(增加)字段“序列”. “序列”有“索引”类型,所以不能这样做：

UPDATE "table" SET "sequence" = "sequence" + 1 WHERE "CableLine" = 2

当id = 10的行中的“序列”增加1时,我收到一个错误,即“sequence”= 2的另一行已经存在.

UPDATE tbl SET "sequence" = ("sequence" + 1) * -1
WHERE  "CableLine" = 2;

UPDATE tbl SET "sequence" = "sequence" * -1
WHERE  "CableLine" = 2
AND    "sequence" < 0;