在Postgres中使用DELETE的返回值进行UPDATE操作

Question

我需要使用从另一个表中删除的值来更新表格。情况类似于SO上的评论投票计分器。我使用Python来处理Postgres，但这不应该有任何影响。

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)

错误出现在(DELETE FROM语句中，出现了语法错误。我可以将DELETE语句替换为SELECT语句，然后它就可以正常工作了，这里有什么我错过的地方吗？我想在更新中使用返回的值。这可能吗？任何帮助都很重要。

相关模式：

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

history.vote 通常为 1 或 -1。

- JDong

Answer 1

PostgreSQL不允许将更新和删除语句混合作为子查询。

可以使用稍微不同的策略-可更新CTE

postgres=# WITH t1 AS (DELETE FROM foo RETURNING *), 
                t2 AS (INSERT INTO deleted 
                          SELECT * FROM t1 RETURNING *) 
             SELECT max(a) FROM t2;

所以

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)

注意：需要9.1或更高版本

- Pavel Stehule

---

这个程序按预期工作，原子地从历史记录中删除投票并更新投票得分。谢谢！ - JDong

哇，使用 WITH 语句可以在一个语句中完成如此多的操作，真是太神奇了。 - danneu