Switching foreign keys of two rows

Question

I am working on a site where users can trade items with other users. Upon completion of a trade, I need to change the owner of the items.

My 'items' table has a foreign key referencing the user. Given two item id's, how can I switch the foreign keys with each other?

I've tried something like this...

UPDATE items
SET user_id = (
  SELECT users.id FROM items
  INNER JOIN users
  ON users.id = items.user_id
  WHERE items.id = $1
)
WHERE id = $2;

UPDATE items
SET user_id = (
  SELECT users.id FROM items
  INNER JOIN users
  ON users.id = items.user_id
  WHERE items.id = $2
)
WHERE id = $1;

This doesn't work because after the first UPDATE is performed, the user_id is updated, so the second SELECT statement does not work as intended.

Answer 1

You should do that in a single query, use with statement.

The pairs query links items (given as parameters) with users and switches the linked values by aggregating them in opposite orders.

with params(param) as (
    values ($1), ($2)
),

pairs(i_id, u_id) as (
    select i_id, u_id
    from (
        select 
            array_agg(param order by user_id) as i_arr, 
            array_agg(user_id order by user_id desc) as u_arr
        from items
        join params on param = id
    ) s,
    unnest(i_arr, u_arr) as u(i_id, u_id)
)

update items
set user_id = u_id
from pairs
where id = i_id;