Delete data when self-referencing table

I've run into a problem trying to clean up data from a MySQL table. The problem is that we need to use the table in the subquery to identify what needs to be deleted.

DELETE 
FROM dates
WHERE event_ref IN (*list of event_refs*)
AND person_ref IN (
  SELECT person.person_ref
  FROM person
  JOIN dates ON dates.person_ref = person.person_ref AND dates.event_ref = 14
  WHERE dates.date < CURDATE()
)

The Subquery gives all people with a date earlier than today. The list of event refs is a list of events that we want to delete, and which excludes event_ref = 14

Treating the query as a SELECT returns everything we want to delete, but MySQL complains when try to use the delete.

I don't really want to go to the length of creating a view to reference into the above query as the clean up is a one time event

Solution

First let's shorten the query. You can get rid of the person table here; there is nothing you do with it.

DELETE 
FROM dates
WHERE event_ref IN (*list of event_refs*)
AND person_ref IN
(
  SELECT person_ref
  FROM dates 
  WHERE event_ref = 14 AND date < CURDATE()
);

Now MySQL doesn't like to directly access a table it is deleting from. In MySQL you'll have to wrap the table hence in a pseudo subquery:

DELETE 
FROM dates
WHERE event_ref IN (*list of event_refs*)
AND person_ref IN
(
  SELECT person_ref
  FROM (SELECT * FROM dates) d
  WHERE event_ref = 14 AND date < CURDATE()
);