CODEWITHSUNDEEP
phpmysqlsql
Loki
Loki

Reputation: 53

Problems with deleting old records from MySQL

Each time a php script runs i want to delete old entries by id. But MySQL throws me this error: 

#1111 - Invalid use of group function

This is the query:

DELETE FROM am_shoutbox WHERE MAX(msg_id)-160 > msg_id

What is the problem here? I tried around and solved it by selecting the highest id to php first and then delete with a second query, but for better performance i want to do this in one if possible.

I hope someone can figure out what is wrong with the query above.

Upvotes: 0

Views: 73

Answers (2)

Diego Duarte
Diego Duarte

Reputation: 477

The invalid use of group function the error describes is due use of MAX(msg_id) in WHERE clause. You can use it either in a select/subselect or in the HAVING clause:

    delete from am_shoutbox 
    where 
    (select (max(ams.msg_id) FROM am_shoutbox ams) - 160) > msg_id

*you may need to specify a table alias in the sub-query as above

or the more elegant and better performance way:

    delete from am_shoutbox 
    having (max(msg_id) - 160) > msg_id

Upvotes: 0

potashin
potashin

Reputation: 44581

You can't use aggregate functions in the WHERE clause. You can try something like this (using subquery that is retrieving MAX(msg_id)) :

 DELETE FROM am_shoutbox 
 WHERE ( SELECT * 
         FROM ( SELECT MAX(msg_id) 
                FROM am_shoutbox ) m ) - 160 > msg_id

Upvotes: 3

Related Questions