mysql advanced delete

Question

hopefully you can get what I want to do from the mysql query below.

delete d1 from 
D as d1, D as d2
left join C rel1 on rel1.tld_tod_id=d1.did
left join C rel2 on rel2.tld_tod_id=d2.did
where d1.did!=d2.did and d1.name=d2.name
and rel1.cid is null and rel2.cid is not null
;

I got unknown column d1.did in on clause, how do I get it right?

Answer 1

Using as is for column aliases. For table aliases just put the alias behind the table

delete d1 from 
D d1, D d2
left join C rel1 on rel1.tld_tod_id=d1.did
left join C rel2 on rel2.tld_tod_id=d2.did
where d1.did!=d2.did and d1.name=d2.name
and rel1.cid is null and rel2.cid is not null
;

If I understand it right this will delete rows in the table D with a duplicate name if it has no relation with table C. However if that's the case shouldn't the joins have to be OUTER?

Answer 2

Use a subselect:

DELETE FROM D AS D1 WHERE did IN (
  SELECT did FROM D as d1, D as d2
  LEFT JOIN C rel1 ON rel1.tld_tod_id=d1.did
  LEFT JOIN C rel2 ON rel2.tld_tod_id=d2.did
 WHERE d1.did!=d2.did AND d1.name=d2.name
 AND rel1.cid is null and rel2.cid is not null
)

the SELECT query returns a list of ids, which is used in the DELETE statement. The SELECT must select just one field for every record that is to be deleted.

An advantage in doing it this way is that you can test the select part independently to ensure you're picking out the right records, and then just slot it into the delete statement.