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
解决方法
至于标题提出的问题:sql UPDATE命令中没有ORDER BY. Postgres以任意顺序更新行.但是您有(有限的)选项来决定是在每行之后,每个语句之后还是在事务结束时检查约束.您可以使用DEFERRABLE约束避免中间状态的重复键冲突.
我引用了我们在这个问题下制定的内容:
Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
>在每行之后检查NOT DEFERRED约束.
>在每个语句后检查设置为IMMEDIATE(INITIALLY IMMEDIATE或通过SET CONSTRAINTS)的DEFERRABLE约束.
但是有一定的局限性.外键约束要求对目标列具有不可延迟的约束.
The referenced columns must be the columns of a non-deferrable unique
or primary key constraint in the referenced table.
问题更新后更新.
假设“序列”在正常操作中从不消极,您可以避免这样的唯一错误:
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()的间隙的数字.考虑这个相关的答案:
Is it possible to use a PG sequence on a per record label?