update table with limit and offset in postgres

Question

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

i want to update a column but only the first 100 rows with an offset from a subquery like

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

im using postgres 8.4

Answer 1

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

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;