在Postgres中使用DELETE的返回值进行UPDATE [英] Using return value from DELETE for UPDATE in Postgres
问题描述
我需要使用从另一个表中删除的值来更新表。这种情况是评论投票记分员,类似于SO。我正在使用python处理postgres,但这不会有所作为。
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)
错误出现在(DELETE FROM
;出现语法错误。我可以将 DELETE
语句替换为 SELECT
语句,它将起作用,我在这里缺少什么吗?我想在更新中使用返回值。这可能吗?有什么帮助。
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.
事件模式:
CREATE TABLE history (
commentId bigint,
vote int,
userIdentity varchar(256),
);
CREATE TABLE comment (
id bigint,
score bigint,
);
history.vote通常为 1
或 -1
。
history.vote is normally 1
or -1
.
推荐答案
PostgreSQL不允许混合使用UPDATE和DELETE语句作为子查询。
PostgreSQL doesn't allow mix UPDATE and DELETE statements as subquery.
您可以使用一些不同的策略-可更新的CTE
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;
所以
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或更高版本
Attention: It require 9.1 or newer
这篇关于在Postgres中使用DELETE的返回值进行UPDATE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!