用ORDER BY更新

小编典典

用ORDER BY更新

sql

需要“配合”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增加1时，1我收到一条错误，指出"sequence" = 2已存在另一行。

阅读 223

2021-05-05

共1个答案

小编典典

`UPDATE` 和 `ORDER BY`

至于所提问题的标题：ORDER BYSQLUPDATE命令中没有。Postgres以任意顺序更新行。但是，您有（有限的）选项来决定是在每行之后，每条语句之后还是在事务结束时检查约束。您可以避免带有约束的
中间状态重复键冲突DEFERRABLE。

我引用了我们在此问题下得出的结论：

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列进行行的相对排序比较明智。您可以row_number()在视图或查询中使用窗口函数生成以1开头且没有空格的数字。考虑以下相关答案：

您应该重新考虑您的方法。您的表格只需一个简单的序列，然后将其结合起来user_id即可获得所需的排序顺序。

您始终可以使用row_number()简单的查询（例如：）来生成具有所需数字的自定义网址：

SELECT format('domain.me/username_%s/posts/%s'
              ,user_id
              ,row_number() OVER (PARTITION BY user_id ORDER BY seq_id)
             )
FROM   t;

2021-05-05