用 ORDER BY 更新 [英] UPDATE with ORDER BY

问题描述

需要打领带"；UPDATE 与 ORDER BY.我正在尝试使用游标，但出现错误:

<块引用>

光标cursupd"没有指定行，SQL状态:24000

代码:

开始；DECLARE cursUpd CURSOR FOR SELECT * FROM "table"WHERE 场"= 5760 与序列"；>= 0 AND 序列"<9 按顺序"排序降序；更新表"SET序列"；=序列"；+ 2 cursUpd 的当前位置；关闭 cursUpd;犯罪;

如何正确做?

更新 1

没有光标，当我这样做时:

更新CableLinePoint"ASt"SET序列"；= t.序列"；+ 2从 (select max("sequence") "sequence", "id";来自CableLinePoint"在哪里电缆线"= 5760按id"分组按顺序"排序DESC)的"其中t".=s".id"和t".=s".序列"

我得到了唯一的错误.所以，需要从头更新，而不是从头更新.

更新 2

表:

id|CableLine|sequence10|2 |111|2 |212|2 |313|2 |414|2 |5

需要更新(增加)字段sequence".序列"有索引"类型，所以无法完成:

更新表"SET序列"；=序列"；+ 1 WHERE CableLine"= 2

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

解决方案

UPDATE with ORDER BY

关于标题提出的问题:SQL UPDATE 命令中没有 ORDER BY.Postgres 以任意顺序更新行.但是您有(有限的)选项来决定是在每行之后、每条语句之后还是在事务结束时检查约束.您可以使用 DEFERRABLE 约束来避免 intermediate 状态的重复键违规.

我引用了我们在这个问题下得出的结论:

• 约束定义 DEFERRABLE INITIALLY IMMEDIATE 仍然是 DEFERRED?

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

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

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

<块引用>

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

解决方法

^{问题更新后更新.}
假设 "sequence" 在正常操作中永远不会是负数，您可以避免这样的独特错误:

UPDATE tbl SET "sequence" = ("sequence" + 1) * -1其中电缆线"= 2；更新 tbl SET "sequence" = "sequence" * -1其中电缆线"= 2AND序列" <0;

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

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

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

避免问题

在更大的规模或并发负载较重的数据库中，使用 serial 列用于行的相对排序.您可以在视图或查询中使用窗口函数 row_number() 生成从 1 开始且没有间隙的数字.考虑这个相关的答案:

• 是否可以在每个唱片公司上使用 PG 序列?

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

Code:

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;

How to do it correctly?

UPDATE 1

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.

UPDATE 2

Table:

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

Need to update (increase) the field "sequence". "sequence" have "index" type, so cannot be done:

UPDATE "table" SET "sequence" = "sequence" + 1 WHERE "CableLine" = 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 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 am quoting what we worked out under this question:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

• NOT DEFERRED constraints are checked after each row.

• 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.

Aside:
It's OK to skip the key word AS for table aliases, but it's discouraged to do the same for column aliases.

I'd advice not to use SQL key words as identifiers, even though that's allowed.

Avoid the problem

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屋！