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
.