Having trouble deleting rows from single table based on ids from another SELECT

[ultrus]

Hello Gurus,

I'm trying to delete rows from a table, but would need to join in some data from another table (that I don't want to delete from), use an embedded SELECT statement, or run multiple queries (don't want to do that last one).

 

Query:

DELETE FROM event_guest WHERE guest_id IN (SELECT eg.guest_id FROM event_guest eg LEFT JOIN guest g ON g.id = eg.guest_id WHERE g.attendee_id = '2')

 

Error:

#1093 - You can't specify target table 'event_guest' for update in FROM clause

 

But this part works:

(SELECT eg.guest_id FROM event_guest eg LEFT JOIN guest g ON g.id = eg.guest_id WHERE g.attendee_id = '2')

 

Any thoughts on how to restructure this correctly?

 

Thanks a ton in advance!!

 

 

 

[ultrus]

I may have found an answer here:

http://stackoverflow.com/questions/9285143/mysql-you-cant-specify-target-table-a-for-update-in-from-clause