2

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?

Rick James
  • 135,179
  • 13
  • 127
  • 222
Vityata
  • 42,633
  • 8
  • 55
  • 100

3 Answers3

3

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
Vityata
  • 42,633
  • 8
  • 55
  • 100
GMB
  • 216,147
  • 25
  • 84
  • 135
1

EXISTS in there not posible use IN clause, but you need to enclose the table in a seprate select, so that mysql thinks it is another table

delete from ns_cos ns 
where ns.created_at not IN (select max(nsa.created_at) from (SELECT * FROM ns_cos) nsa where nsa.month_year = ns.month_year)
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Error: *You can't specify target table 'ns' for update in FROM clause* - https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0bd7fbc06b98687aa0489bacf265618d – GMB Sep 04 '20 at 17:11
  • Somehow it did not work, gave the standard errors I was getting before. Still, 10x for the efforts :) – Vityata Sep 08 '20 at 15:23
0

This happens when you join tables from two schemas, no matter whether you take aliases for the table names or not.

Error

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...'

delete !ALIAS! from table ALIAS ...

Strangely, only with aliases, you can get around this error in a one-step SQL. Tested in MySQL 5.7.

Try this pattern, using the alias of the table you want to delete from between delete and from:

delete t1 from table t1
join table2 t2
on t1.id = t2.id

Your code would be:

delete ns 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)

Other steps I checked before finding out the alias trick (do not read)

  • I tried it with a view of the other schema's table in the same schema, that is not enough.

  • One way to get around this is to make a copy of the table in the same schema (and delete that copy afterwards).

  • You might also somehow make a full "linked server link" like in T-SQL [linkedservername].DB1.Schema.Table1 at How to join two tables if they are in different schemas Your code is on the same server, but this might still help jumping over to the other schema, untested.

questionto42
  • 7,175
  • 4
  • 57
  • 90