Using return value from DELETE for UPDATE in Postgres
PostgreSQL doesn't allow mix UPDATE and DELETE statements as subquery.
You can use a little bit different strategy - updateable CTE
postgres=# WITH t1 AS (DELETE FROM foo RETURNING *), t2 AS (INSERT INTO deleted SELECT * FROM t1 RETURNING *) SELECT max(a) FROM t2;
so
postgres=# CREATE TABLE comment(id int, score int);CREATE TABLEpostgres=# CREATE TABLE history(id int, comment_id int, vote int);CREATE TABLEpostgres=# INSERT INTO comment values(1,10);INSERT 0 1postgres=# INSERT INTO comment values(2,20);INSERT 0 1postgres=# INSERT INTO history values(1,1,5);INSERT 0 1postgres=# WITH t1 AS (DELETE FROM history WHERE id=1 RETURNING comment_id, vote) UPDATE comment SET score=score-t1.vote FROM t1 WHERE t1.comment_id=comment.id;UPDATE 1postgres=# select * from comment; id | score ----+------- 2 | 20 1 | 5(2 rows)
Attention: It require 9.1 or newer