postgreSQL select works but delete does not

Question

I'm trying to change the following query into one that deletes the returned rows.

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

this select works and gives me the rows I want to delete. Now if i change the select into a delete my query does not work.

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

ERROR: syntax error at or near "bad_rows" LINE 1: DELETE bad_rows.*

Andomar · Accepted Answer · 2010-10-25 14:27:09Z

3

You can't specify columns to delete (the * in bad_rows.*.)

Also, most databases do not allow you to specify an alias for deleting. So the usual approach to deleting is something like:

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

answered Oct 25, 2010 at 14:27

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stephan · Accepted Answer · 2010-10-25 14:36:08Z

0

Using the advice from Andomar I got it working.

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
)

answered Oct 25, 2010 at 14:36

Stephan

3,8293 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

postgreSQL select works but delete does not

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related