mulit-update-returning.sql

CREATE TABLE aaa (x INT, y INT);
/*
CREATE TABLE
*/
INSERT INTO aaa VALUES (1,1), (2,2), (3,3);
/*
INSERT 0 3
*/
SELECT * FROM aaa;
/*
 x | y 
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)
*/
UPDATE aaa SET y = u.new_y
FROM (
  SELECT new.x AS x, old.y AS old_y, new.y AS new_y
  FROM aaa old, (VALUES (2, 4), (3, 6)) AS new(x, y)
  WHERE old.x = new.x
  FOR UPDATE
  ) AS u
WHERE aaa.x = u.x
RETURNING u.x, u.old_y;
/*
 x | old_y 
---+-------
 2 |     2
 3 |     3
(2 rows)

UPDATE 2
*/
SELECT * FROM aaa;
/*
 x | y 
---+---
 1 | 1
 2 | 4
 3 | 6
(3 rows)
*/