James111 James111 - 1 year ago 48
MySQL Question

Delete from MySql table using aggregate functions?

I'm wondering if it's possible to delete columns using some aggregate functions (found here: http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html)

For example say I wanted to delete the last

from my database, it'd be easy if I could just run:

delete from user where id = MAX(id);

But running this will give me the following error:

ERROR 1111 (HY000): Invalid use of group function

Is this possible with mysql or not?

Answer Source

You have to use a query like this:

delete from user where id = (select id 
                             from (select MAX(id) as id 
                                   from user) as t)