用ORDER BY更新 [英] UPDATE with ORDER BY
问题描述
需要使用 ORDER BY 绑定 UPDATE 。我正在尝试使用游标，但出现错误：
Need to "tie" UPDATE with ORDER BY. I'm trying to use cursors, but get the error:
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;
如何正确执行？
在没有光标的情况下，当我这样做时：
Without cursor, when I do like this:
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"
我得到了唯一的错误。因此，需要从头开始而不是从头开始更新。
I get the unique error. So, need to update from the end rather than from the beginning.
表：
id|CableLine|sequence
10| 2 | 1
11| 2 | 2
12| 2 | 3
13| 2 | 4
14| 2 | 5
需要更新（增加）字段 sequence。 序列具有索引类型，因此无法完成：
Need to update (increase) the field "sequence". "sequence" have "index" type, so cannot be done:
UPDATE "table" SET "sequence" = "sequence" + 1 WHERE "CableLine" = 2
当 sequence排在 1 我收到一个错误，提示另一行 sequence = 2 已经存在。
When "sequence" in the row with id = 10 is incremented by 1 I receive an error that another row with "sequence" = 2 already exists.
推荐答案
UPDATE 与 ORDER BY
关于所提问题的标题：没有 ORDER BY UPDATE 命令中的c $ c>。 Postgres以任意顺序更新行。但是您有（有限的）选项来决定是否在每一行之后，每个语句之后或在事务结束时检查约束。您可以使用 DEFERRABLE 约束来避免 intermediate 状态的重复键冲突。
UPDATE with ORDER BY
As to the question raised ion the title: There is no ORDER BY in an SQL UPDATE command. Postgres updates rows in arbitrary order. But you have (limited) options to decide whether constraints are checked after each row, after each statement or at the end of the transaction. You can avoid duplicate key violations for intermediate states with a DEFERRABLE constraint.
I引用我们在此问题下得出的结论：
I am quoting what we worked out under this question:
在每行之后检查 NOT DEFERRED 约束。
<$ c将$ c> DEFERRABLE 约束设置为立即（设置约束）在每个语句后被检查。
DEFERRABLE constraints set to IMMEDIATE (INITIALLY IMMEDIATE or via SET CONSTRAINTS) are checked after each statement.
但是有局限性。外键约束对目标列要求不可延迟约束。
There are limitations, though. Foreign key constraints require non-deferrable constraints on the target column(s).
引用的列必须为引用表中不可延迟的唯一
或主键约束的列。
The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.
解决方法
在问题更新后更新。
假设序列 在正常操作中，您可以避免这样的独特错误：
Workaround
Updated after question update.
Assuming "sequence" is never negative in normal operation, you can avoid unique errors like this:
UPDATE tbl SET "sequence" = ("sequence" + 1) * -1
WHERE "CableLine" = 2;
UPDATE tbl SET "sequence" = "sequence" * -1
WHERE "CableLine" = 2
AND "sequence" < 0;
在具有不可延迟约束（默认）的情况下，您必须运行两个单独的命令才能完成此工作。快速连续运行命令以避免并发问题。该解决方案显然不适合繁重的并发负载。
With a non-deferrable constraint (default), you have to run two separate commands to make this work. Run the commands in quick succession to avoid concurrency issues. The solution is obviously not fit for heavy concurrent load.
此外：
可以跳过关键字 AS 用于表别名，但不建议对列别名执行相同的操作。
Aside:
It's OK to skip the key word AS for table aliases, but it's discouraged to do the same for column aliases.
我建议不要使用SQL关键字作为标识符，甚至
I'd advice not to use SQL key words as identifiers, even though that's allowed.
在更大的规模上或对于具有高并发负载的数据库，明智的做法是使用 serial 列，用于行的相对排序。您可以在视图或查询中使用窗口函数 row_number（）生成以1开头且无间隔的数字。考虑以下相关答案：
On a bigger scale or for databases with heavy concurrent load, it's wiser to use a serial column for relative ordering of rows. You can generate numbers starting with 1 and no gaps with the window function row_number() in a view or query. Consider this related answer:
- Is it possible to use a PG sequence on a per record label?
这篇关于用ORDER BY更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
