在 Postgres 中使用 DELETE 的返回值进行更新 [英] Using return value from DELETE for UPDATE in Postgres

问题描述

我需要使用从另一个表中删除的值来更新一个表.这种情况是类似于 SO 的评论投票记分员.我正在使用 python 来处理 postgres，但这不应该有所作为.

查询="""UPDATE 注释 SET score=score-(DELETE FROM historyWHERE commentId=%(commentId)s ANDuserIdentity=%(userIdentity)s RETURNING vote)WHERE commentId=%(commentId)s;"""cursor.execute(查询，数据)

(DELETE FROM 处出现错误；出现语法错误.我可以用 SELECT 语句替换 DELETE 语句，它会工作，我这里有什么遗漏吗?我想在更新中使用返回值.这可能吗?有什么帮助.

相关架构:

创建表历史 (commentId bigint,投票整数，用户身份 varchar(256),);创建表注释 (标识大整数，得分大，);

history.vote 通常是 1 或 -1.

解决方案

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

您可以使用一些不同的策略 - 可更新 CTE

<上一页>postgres=# WITH t1 AS (DELETE FROM foo RETURNING *),t2 AS (INSERT INTO 已删除选择 * 从 t1 返回 *)从 t2 中选择 max(a)；

所以

<上一页>postgres=#CREATE TABLE 评论(id int，score int)；创建表postgres=# CREATE TABLE 历史(id int，comment_id int，vote int)；创建表postgres=#插入评论值(1,10)；插入 0 1postgres=#插入评论值(2,20)；插入 0 1postgres=#插入历史值(1,1,5)；插入 0 1postgres=# WITH t1 AS (从历史中删除哪里 id=1返回comment_id，投票)更新评论 SET score=score-t1.vote从 t1哪里 t1.comment_id=comment.id;更新 1postgres=# 从评论中选择 *；编号 |分数----+-------2 |201 |5(2 行)

注意:需要 9.1 或更高版本

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.

解决方案

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

这篇关于在 Postgres 中使用 DELETE 的返回值进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！