Constraint and offset update table in postgres

Question

hi is it possible to update a table with values ​​from another table with restriction and offset?

for example, the t_device table 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 the subquery, for example

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 can not find a solution to modify the value in t_device from t_object

im using postgres 8.4

Answer 1

• This UPDATE statement does not look like it will be useful if you just set id_object to id_object, albeit in a semi-random order.
• If the UPDATE statement of the t_device table does not have a unique WHERE predicate, there is no guarantee that only 100 rows will be updated, which I assume the purpose of the external LIMIT is.
• You should have ORDER BY in the subquery to make sure that you are not getting overlapping rows.

There might be something like the following 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; 

Answer 2

I solved it with rowNumber column generation and updated it with incremental values ​​(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