代码之家 › 专栏 › 技术社区 › Stephan

postgreSQL select起作用,但delete不起作用

postgresql sql

Stephan · 技术社区 · 14 年前

我正试图将以下查询更改为删除返回行的查询。

SELECT bad_rows.*
FROM votes as bad_rows
INNER JOIN(
    SELECT  MIN(id) as min_id, votes.object_id, votes.user_id 
FROM votes WHERE is_archived=FALSE AND 
     votes.direction < 20 
GROUP BY votes.object_id, votes.user_id, votes.content_type_id
having COUNT(votes.object_id) > 1
) AS double_rows ON 
    double_rows.object_id = bad_rows.object_id 
    AND double_rows.user_id=bad_rows.user_id 
    AND bad_rows.is_archived=False
    AND double_rows.min_id <> bad_rows.id

此选择有效,并为我提供要删除的行。现在,如果将select更改为delete,则查询将不起作用。

DELETE bad_rows.*
FROM votes as bad_rows
INNER JOIN(
    SELECT  MIN(id) as min_id, votes.object_id, votes.user_id 
FROM votes WHERE is_archived=FALSE AND 
     votes.direction < 20 
GROUP BY votes.object_id, votes.user_id, votes.content_type_id
having COUNT(votes.object_id) > 1
) AS double_rows ON 
    double_rows.object_id = bad_rows.object_id 
    AND double_rows.user_id=bad_rows.user_id 
    AND bad_rows.is_archived=False
    AND double_rows.min_id <> bad_rows.id

第1行:删除错误的行*

2 回复 | 直到 14 年前

Andomar 14 年前

不能指定要删除的列 * 在里面 bad_rows.* .)

此外,大多数数据库不允许您指定要删除的别名。所以通常的删除方法如下:

DELETE  VOTES
WHERE   ID IN
        (
        <subquery selecting the ID's of the rows you want to delete>
        )

Stephan 14 年前

根据安多玛的建议,我成功了。

DELETE FROM votes WHERE id IN (
SELECT bad_rows.id
FROM votes as bad_rows
INNER JOIN(
SELECT  MIN(id) as min_id, votes.object_id, votes.user_id 
FROM votes where is_archived=FALSE and votes.direction < 20 

GROUP BY votes.object_id, votes.user_id, votes.content_type_id
having COUNT(votes.object_id) > 1
) as double_rows on double_rows.object_id = bad_rows.object_id and double_rows.user_id=bad_rows.user_id and bad_rows.is_archived=False
    and double_rows.min_id <> bad_rows.id
)

推荐文章

Community wiki · 我应该写更多的SQL来提高效率,还是应该写更少的SQL来减少bug?

1 年前

Community wiki · SQL语法新手

1 年前

Thomas Leplus · PLS-00306:过程调用中的参数数量或类型错误

1 年前

KateMak · 是否将多行中的多列与唯一id组合?

1 年前

Mateusz UrbaÅski · 在PostgreSQL中按计算经验分组

1 年前

Karuna · SQL中列内的筛选器[重复]

1 年前

Irvan Affandy · 为另一个选择选择声明的键

1 年前

Community wiki · 这个MySQL语句出了什么问题?

1 年前

user22329205 · 如何使用Google BigQuery中的条件,根据特定列值连接列的N行?

1 年前

Community wiki · 优化从同一表中提取的多列的查询

1 年前