Toad World® Forums

DELETE with join on Local Storage

I need to do some cleanup in a Local Storage (MySQL) table. Something like this:
DELETE All
FROM MissingFARid.All All_A
INNER JOIN MissingFARid.All All_Other ON (All_A.KID = All_Other.KID)
WHERE (All_A.RID = 'A')
AND (All_Other.RID <> 'A')
AND All_A.KUNDEADR_TYPE = All_Other.KUNDEADR_TYPE

This gives the error message: "Unknown table 'all' in MULTI DELETE".

The following variants also fail:
DELETE FROM MissingFARid.All All_A
WHERE All_A.RID = 'A' and All_A.KID in
(select KID
from MissingFARid.All All_other
where (All_Other.RID <> 'A')
AND All_A.KUNDEADR_TYPE = All_Other.KUNDEADR_TYPE)

DELETE MissingFARid.All
FROM MissingFARid.All All_A
LEFT JOIN MissingFARid.All All_Other ON (All_A.KID = All_Other.KID)
WHERE All_A.RID = 'A'
and All_Other.RID <> 'A')
AND All_A.KUNDEADR_TYPE = All_Other.KUNDEADR_TYPE)

DELETE FROM MissingFARid.All All_A
WHERE All_A.RID = 'A' and exists
(select KID
from MissingFARid.All All_other
where (All_Other.RID <> 'A')
AND All_A.KUNDEADR_TYPE = All_Other.KUNDEADR_TYPE)

So how do I create such a delete with join?

Arne