SQL 使用 ORDER BY 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16735950/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UPDATE with ORDER BY
提问by dedoki
Need to "tie" UPDATEwith ORDER BY. I'm trying to use cursors, but get the error:
需要UPDATE与ORDER BY. 我正在尝试使用游标，但出现错误：
cursor "cursupd" doesn't specify a line, SQL state: 24000
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
更新 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
更新 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 = 10is incremented by 1I receive an error that another row with "sequence" = 2already exists.
当行中的“序列”id = 10增加时，1我收到另一个行"sequence" = 2已经存在的错误。
采纳答案by Erwin Brandstetter
UPDATEwith ORDER BY
UPDATE和 ORDER BY
As to the question raised ion the title: There is no ORDER BYin an SQL UPDATEcommand. 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 intermediatestates with a DEFERRABLEconstraint.
对于标题提出的问题：ORDER BYSQLUPDATE命令中没有。Postgres 以任意顺序更新行。但是您有（有限的）选项来决定是否在每行之后、每条语句之后或在事务结束时检查约束。您可以使用约束避免中间状态的重复键冲突DEFERRABLE。
I am quoting what we worked out under this question:
我引用了我们在这个问题下的研究结果：
Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
NOT DEFERREDconstraints are checked after each row.DEFERRABLEconstraints set toIMMEDIATE(INITIALLY IMMEDIATEor viaSET CONSTRAINTS) are checked after each statement.
NOT DEFERRED在每一行之后检查约束。DEFERRABLE在每个语句之后检查设置为IMMEDIATE(INITIALLY IMMEDIATE或 viaSET CONSTRAINTS) 的约束。
There are limitations, though. Foreign key constraints require non-deferrableconstraints 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:
问题更新后更新。
假设"sequence"在正常操作中从不为负，您可以避免这样的独特错误：
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 ASfor table aliases, but it's discouraged to do the same for column aliases.
旁白：
可以跳过AS表别名的关键字，但不鼓励对列别名执行相同操作。
I'd advice not to use SQL key words as identifiers, even though that's allowed.
我建议不要使用 SQL 关键字作为标识符，即使这是允许的。
Avoid the problem
避免问题
On a bigger scale or for databases with heavy concurrent load, it's wiser to use a serialcolumn 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:
在更大的规模上或对于并发负载很重的数据库，使用serial列来对行进行相对排序是更明智的做法。您可以row_number()在视图或查询中使用窗口函数生成从 1 开始且没有间隙的数字。考虑这个相关的答案：
回答by alexkovelsky
UPDATEwith ORDER BY:
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;
UPDATEorder is still random (I guess), but the values supplied to UPDATEcommand are matched by thetable.rowid=t1.rowidcondition. So what I am doing is, first selecting the 'updated' table in memory, it's named t1in the code above, and then making my physical table to look same as t1. And the update order does not matter anymore.
UPDATEorder 仍然是随机的（我猜），但提供给UPDATEcommand的值按thetable.rowid=t1.rowid条件匹配。所以我正在做的是，首先选择内存中的“更新”表，它t1在上面的代码中命名，然后让我的物理表看起来与t1. 更新顺序不再重要。
As for true ordered UPDATE, I don't think it could be useful to anyone.
至于真正的ordered UPDATE，我认为它对任何人都没有用。
回答by Syed Najam ul Hassan
Update with Order By
Declare
v number;
cursor c1 is
Select col2 from table1 order by col2;
begin
v:=0;
for c in c1
loop
update table1
set col1 =v+1
where col2 = c.col2;
end loop;
commit;
END;
回答by ssi-anik
If anyone comes here just like I came for the problem with rearranging the postgresql table_id_seq from 1 and order by the id. The solution I tried was partially taken from @Syd Nazam Ul Hasan (above) and https://gist.github.com/JoshCheek/e19f83f271dc16d7825e2e4079538ba8.
如果有人像我一样来这里解决重新排列 postgresql table_id_seq 从 1 并按 id 排序的问题。我尝试的解决方案部分来自@Syd Nazam Ul Hasan（上图）和https://gist.github.com/JoshCheek/e19f83f271dc16d7825e2e4079538ba8。
CREATE OR REPLACE FUNCTION update_sequence()
RETURNS SETOF varchar AS $$
DECLARE
curs CURSOR FOR SELECT * FROM table ORDER BY id ASC;
row RECORD;
v INTEGER := 0;
BEGIN
open curs;
LOOP
FETCH FROM curs INTO row;
update table
set id = v+1
where id = row.id;
v = v+1;
EXIT WHEN NOT FOUND;
return next row.id;
END LOOP;
END; $$ LANGUAGE plpgsql;
SELECT update_sequence();
回答by user3605589
Lazy Way, (aka not fastest or best way)
懒惰的方式，（也不是最快或最好的方式）
CREATE OR REPLACE FUNCTION row_number(table_name text, update_column text, start_value integer, offset_value integer, order_by_column text, order_by_descending boolean)
RETURNS void AS
$BODY$
DECLARE
total_value integer;
my_id text;
command text;
BEGIN
total_value = start_value;
command = 'SELECT ' || order_by_column || ' FROM ' || table_name || ' ORDER BY ' || order_by_column;
if (order_by_descending) THEN
command = command || ' desc';
END IF;
FOR my_id in EXECUTE command LOOP
command = 'UPDATE ' || table_name || ' SET ' || update_column || ' = ' || total_value || ' WHERE ' || order_by_column || ' = ' || my_id|| ';';
EXECUTE command;
total_value = total_value + offset_value;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Example
例子
SELECT row_number('regispro_spatial_2010.ags_states_spatial', 'order_id', 10,1, 'ogc_fid', true)
SELECT row_number('regispro_spatial_2010.ags_states_spatial', 'order_id', 10,1, 'ogc_fid', true)
回答by cmore
This worked for me:
这对我有用：
[update statement here] OPTION (MAXDOP 1) -- prevent row size from causing use of an eager spool, which mutilates the order in which records are updated.
[此处的更新语句] OPTION (MAXDOP 1) -- 防止行大小导致使用急切假脱机，这会破坏记录更新的顺序。
I use a clustered int index in sequential order (generating one if needed) and hadn't had a problem until recently, and even then only on small rowsets that (counterintuitively) the query plan optimizer decided to use a lazy spool on.
我按顺序使用聚集的 int 索引（如果需要，生成一个）并且直到最近才遇到问题，即使如此，仅在（违反直觉的）查询计划优化器决定使用惰性假脱机的小行集上。
Theoretically I could use the new option to disallow spool use, but I find maxdop simpler.
理论上我可以使用新选项来禁止使用假脱机，但我发现 maxdop 更简单。
I am in a unique situation because the calculations are isolated (single user). A different situation may require an alternative to using maxdop limit to avoid contention.
我处于一种独特的情况，因为计算是孤立的（单个用户）。不同的情况可能需要使用 maxdop 限制的替代方法来避免争用。
