使用ORDER BY更新

需要"配合" UPDATE用ORDER BY.我正在尝试使用游标,但得到错误:

cursor "cursupd" doesn't specify a line,
SQL state: 24000

码:

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增加了1I ,则会收到另一行"sequence" = 2已经存在的错误.

1> alexkovelsky..：

---

UPDATE用ORDER BY:

UPDATE thetable 
  SET columntoupdate=yourvalue 
 FROM (SELECT rowid, 'thevalue' AS yourvalue 
         FROM thetable 
        ORDER BY rowid
      ) AS t1 
WHERE thetable.rowid=t1.rowid;

UPDATE顺序仍然是随机的(我猜),但提供给UPDATE命令的值与thetable.rowid=t1.rowid条件匹配.所以我要做的是,首先在内存中选择'updated'表,t1在上面的代码中命名,然后使我的物理表看起来像t1.更新顺序不再重要.

至于真正的有序UPDATE,我不认为它对任何人都有用.

2> Erwin Brands..：

---

UPDATE 同 ORDER BY

至于标题提出的问题:ORDER BYSQL UPDATE命令中没有.Postgres以任意顺序更新行.但是您有(有限的)选项来决定是在每行之后,每个语句之后还是在事务结束时检查约束.您可以避免对具有约束的中间状态的重复键冲突DEFERRABLE.

我引用了我们在这个问题下得出的结论:
约束定义的DEFERRABLE INITIALLY IMMEDIATE仍然是DEFERRED？

NOT DEFERRED在每行之后检查约束.

DEFERRABLE在每个语句之后检查设置为IMMEDIATE(INITIALLY IMMEDIATE或通过SET CONSTRAINTS)的约束.

但是有一些限制.外键约束要求对目标列进行不可延迟的约束.

引用的列必须是引用表中不可延迟的唯一或主键约束的列.

解决方法

^{问题更新后更新.}
假设"sequence"在正常操作中从不消极,您可以避免这样的唯一错误:

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

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

使用不可延迟的约束(默认),您必须运行两个单独的事务才能使其工作.快速连续运行命令以避免并发问题.该解决方案显然不适合重载并发负载.

旁白:
跳过AS表别名的关键字是可以的,但是不建议对列别名执行相同的操作.

我建议不要使用SQL关键字作为标识符,即使这是允许的.

避免这个问题

在更大规模或具有大量并发负载的数据库中,使用serial列进行行的相对排序更明智.您可以生成以1开头的数字,并且row_number()在视图或查询中没有与窗口函数的间隙.请考虑以下相关答案:
是否可以在每个记录标签上使用PG序列？

---

FWIW,`UPDATE ...`中缺少`ORDER BY`是一个真正的PITA,并且更难以避免同时更新同一个表的查询之间的死锁.能够为"更新"指定一致的锁定顺序对于某些工作负载的性能来说是一个巨大的胜利 - 排序会花费,但不会像所有查询重试那样远程.