CODEWITHSUNDEEP
sqlpostgresqlsubquerysql-delete
SHADOWSLIFER
SHADOWSLIFER

Reputation: 17

delete records from a select query

i need to delete some users by counting the column "command", if there are less then 5 entry, they should be deleted from the table "Users".

DELETE FROM public."Users" 
where telegram_nickname in (select telegram_nickname, 
                                   count (command) 
                            from public."Logs" 
                            group by telegram_nickname 
                            having count (command) > 5 
                            order by (count) desc)

The above query doesn't work.

Upvotes: 0

Views: 60

Answers (2)

user330315
user330315

Reputation:

The IN operator requires that you have the same number of columns on the left side and and the right side. Your sub-query returns two columns, but you only compare it to one column on the left. You need to remove the count(command) from the sub-query's select list - it's not needed anyway.

Also: an order by in a sub-query used for an IN condition is useless (unless you apply e.g. an distinct on () or a limit clause)

So you need to use:

DELETE FROM public."Users" 
where telegram_nickname in (select telegram_nickname
                            from public."Logs" 
                            group by telegram_nickname 
                            having count (command) > 5)

Upvotes: 1

Sandesh Gupta
Sandesh Gupta

Reputation: 1195

Remove count (command) from SELECT. Your SELECT is selecting 2 columns whereas only 1 column should be passed to IN operator,

Remove order by as well. Its pointless when you have to delete something.

DELETE FROM public."Users" 
where telegram_nickname in (select telegram_nickname
                            from public."Logs" 
                            group by telegram_nickname 
                            having count (command) > 5 )

Upvotes: 0

Related Questions