问题描述
我需要使用从另一个表中删除的值来更新一个表.这种情况是类似于 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 的返回值进行更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!