Reordering user-sortable Postgres tables in one `UPDATE` statement

update_sort_order.sql

CREATE TABLE tbl (
  id UUID,
  sort_order INT
);

-- 1 param of type UUID[]: pass in all IDs in the desired order
WITH updated_order(id, new_sort_order) AS (
  SELECT tbl.id, u.new_sort_order
  FROM tbl
  LEFT OUTER JOIN unnest(? :: UUID []) WITH ORDINALITY AS u(id, new_sort_order) on tbl.id = u.id
)
UPDATE tbl
SET sort_order = updated_order.new_sort_order
FROM updated_order
WHERE tbl.id = updated_order.id
AND tbl.sort_order IS DISTINCT FROM updated_order.new_sort_order;