David David - 6 months ago 19
SQL Question

MySQL error on delete with inner join (1066 Non-Unique ID)

Bit of a newbie, attempting to delete rows from two tables. I'm not sure how to alias - which appears to be my problem - any assistance will be helpful!

DELETE emails, emails_bodies
FROM emails, emails_bodies
INNER JOIN emails_bodies ON emails_bodies.email_id = emails.id
WHERE
emails.date_modified < "2013-01-01"


Fails with:

1066: Not unique table/alias: 'emails_bodies'

Answer

You have an extra reference to the table emails_bodies in your DELETE query. Try this:

DELETE emails.*
FROM emails
INNER JOIN emails_bodies ON emails_bodies.email_id = emails.id
WHERE emails.date_modified < "2013-01-01"

I also removed one of the tables from the select list, because MySQL does not delete from two different tables in one query. One option to have this happen would be to set up cascading between a parent table (e.g. emails), and a child table (e.g. emails_bodies). Then deleting a record in emails would automatically delete foreign key references in emails_bodies.

As to why you are getting a Not unique table/alias error, MySQL interpreted your query as something along the lines of the following:

DELETE emails, emails_bodies
FROM emails
INNER JOIN emails_bodies   -- cross join, since no ON condition given
INNER JOIN emails_bodies ON emails_bodies.email_id = emails.id
WHERE emails.date_modified < "2013-01-01"

You were using the same table name twice, without giving each a unique alias.

Comments