Using return value from DELETE for UPDATE in Postgres

Question

I need to update a table using a value deleted from another table. The situation is a comment vote scorekeeper similar to the one on SO. I'm using python to work the postgres, but that shouldn't make a difference.

query="""
UPDATE comment SET score=score-(DELETE FROM history
                                WHERE commentId=%(commentId)s AND
                                      userIdentity=%(userIdentity)s RETURNING vote)
WHERE commentId=%(commentId)s;
"""
cursor.execute(query, data)

The error arises at (DELETE FROM; a syntax error arises. I can replace the DELETE statement with a SELECT statement and it will work, is there something I am missing here? I want to use the returning value in an update. Is this possible? Anything helps.

Relevent schema:

CREATE TABLE history (
    commentId bigint,
    vote int,
    userIdentity varchar(256),
);
CREATE TABLE comment (
    id bigint,
    score bigint,
);

history.vote is normally 1 or -1.

JDong · Accepted Answer · 2013-08-01 15:31:47Z

28

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 TABLE
postgres=# CREATE TABLE history(id int, comment_id int, vote int);
CREATE TABLE
postgres=# INSERT INTO comment values(1,10);
INSERT 0 1
postgres=# INSERT INTO comment values(2,20);
INSERT 0 1
postgres=# INSERT INTO history values(1,1,5);
INSERT 0 1
postgres=# 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 1
postgres=# select * from comment;
 id | score 
----+-------
  2 |    20
  1 |     5
(2 rows)

Attention: It require 9.1 or newer

edited Aug 1, 2013 at 15:31

JDong

2,3343 gold badges26 silver badges43 bronze badges

answered Aug 1, 2013 at 15:04

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JDong Over a year ago

This works as intended, removing the vote in history and updating the vote score atomically. Thanks!

danneu Over a year ago

Wow, it's amazing how much you can do in one statement with WITH.

Collectives™ on Stack Overflow

Using return value from DELETE for UPDATE in Postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related