postgresql 使用 postgres 中的限制和偏移量更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17552428/
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 table with limit and offset in postgres
提问by Ser Yoga
hi is it possible to update a table with values form an other table with limit and offset?
嗨，是否可以使用具有限制和偏移量的其他表的值更新表？
for example the table t_device has 600 rows and t_object has 100 rows
例如表 t_device 有 600 行，t_object 有 100 行
i want to update a column but only the first 100 rows with an offset from a subquery like
我想更新一列，但只有前 100 行与子查询的偏移量，如
update t_device set id_object =
(select id_object from t_object limit 100) limit 100 offset 0;
update t_device set id_object =
(select id_object from t_object limit 100) limit 100 offset 100;
is it possible? i cant find a solution to modive the value in t_device from t_object
是否可以？我找不到从 t_object 修改 t_device 值的解决方案
im using postgres 8.4
我使用的是 postgres 8.4
回答by bma
- That UPDATE statement doesn't look like it is going to be much use if you are just setting id_object to id_object, albeit in a semi-random order.
- If there is no unique WHERE predicate in the t_device table UPDATE statement, there is no guarantee that only 100 rows will be updated, which is what I assume the intent of the outer LIMIT is.
- You should have an ORDER BY in the subquery to ensure you are not getting overlaps in rows.
- 如果您只是将 id_object 设置为 id_object（尽管是半随机顺序），那么该 UPDATE 语句看起来不会有多大用处。
- 如果 t_device 表 UPDATE 语句中没有唯一的 WHERE 谓词，则不能保证只会更新 100 行，这就是我假设外部 LIMIT 的意图。
- 您应该在子查询中使用 ORDER BY 以确保行中不会出现重叠。
Something like the following might work for you.
像下面这样的东西可能对你有用。
UPDATE t_device set id_object = t.id_object
FROM (select id_object from t_object order by id_object limit 100) t
WHERE t.id_object = t_device.id_object;
回答by Ser Yoga
i solved it with generating a column rowNumber and and updated it with incremental values (1, 2, 3)
我通过生成列 rowNumber 解决了它，并用增量值 (1, 2, 3) 更新了它
and then
接着
UPDATE t_device set id_object = t.id_object FROM
(select t1.id_object , row_number() OVER (ORDER BY id_object ) AS rn
from dblink('dbname=*** password=***', 'select id_object from t_object')
as t1(id_object int) order by id_object limit 103) as t
where t_device.rowNumber = t.rn
