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