PostgreSql: how to update table from array?

Question

How to update table fields from array to avoid temp table using? User passes array to reorder table.

create TABLE items
(
id serial primary key,
name text, 
select_order int
)

insert into items(name, select_order)
values
('cucumber',0),
('milk',1),
('coffee',2),
('water',3)

select * from items order by select_order;

DO
$body$
DECLARE var_array int[]:='{3,0,2,1}';
BEGIN
update items ??

END;
$body$

The final result in this example should be

select * from items order by select_order;
name        select_order
------------------------
water       0
cucumber    1
coffee      2
milk        3

Answer 1

Assuming the index in the array corresponds to the value in select_order the following query returns the new and old value:

select i.id, i.select_order, t.new_sort
from items i
   join lateral unnest(array[3,0,2,1]) with ordinality as t(new_sort, idx) on t.idx - 1 = i.select_order

Now this can be used to update the target table:

update items
  set select_order = r.new_sort
from (
  select i.id, i.select_order, t.new_sort
  from items i
    join lateral unnest(array[3,0,2,1]) with ordinality as t(new_sort, idx) on t.idx - 1 = i.select_order
) r
where r.id = items.id;

This also assumes that select_order starts a 0 and has no gaps.