postgresql - 在 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；出现语法错误。我可以用 SELECT 语句替换 DELETE 语句，它将工作，我在这里遗漏了什么吗？我想在更新中使用返回值。这可能吗？任何帮助。

相关架构:

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

history.vote 通常为 1 或 -1。

Answer 1

PostgreSQL 不允许混合使用 UPDATE 和 DELETE 语句作为子查询。

您可以使用一些不同的策略 - 可更新的 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 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)

注意:需要9.1以上