DELETE FROM throws a SQL Error [1064] [42000]: (conn=5159) on a query, on which a SELECT works

Question

Trying to delete a specific amount of rows in a MySQL query, I am able to SELECT whatever I want to delete with the following command, getting the results I need:

select * from ns_cos ns where ns.created_at <>
    (select max(nsa.created_at) from ns_cos nsa
        where nsa.month_year = ns.month_year)

However, when I try to delete the selected data with:

delete from ns_cos ns where ns.created_at not exists
    (select max(nsa.created_at) from ns_cos nsa
        where nsa.month_year = ns.month_year)

I get:

SQL Error [1064] [42000]: (conn=5159) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ns where ns.created_at not exists (select max(nsa.created_at) from ns_cos nsa wh' at line 1

What am I doing wrong?

Answer 1

Your immediate issue is that not all MySQL versions support aliasing the table directly in delete from. Furthermore, though, you cannot re-open the table you delete from in the from clause.

Consider using the delete ... join syntax.

delete ns
from ns_cos ns
inner join (
    select month_year, max(nsa.created_at) created_at
    from ns_cos nsa 
    group by month_year
) ns1 on ns1.month_year = ns.month_year and ns1.created_at <> ns.created_at