Hi All, I'm having trouble forming a delete query that removes rows from a table based on a condition that requires joining 3 tables (including the table to delete from).
The SELECT query that identifies the rows to delete is: SELECT folder_items.folder_id, folder_items.item_id FROM folders, folder_items, past_events WHERE past_events.event_id=folder_items.item_id AND folders.folder_type='events' AND folders.folder_id=folder_items.folder_id; I've tried DELETE FROM folder_items WHERE folder_item IN ( query that selects folder_items) AND folder_id IN ( query that selects folders) and I've also tried selecting the rows to delete into a temporary table and then deleting based on rows in the temporary table but the statements just produce SQL syntax errors. I'm using MySQL 3.23.40 and I know the functionality I need is in 4.0 but I wasn't planning on updating just yet. I can solve this problem by copying the rows I want to keep into a new table, dropping the old table, and renaming the new one but I'm not confident about the synchronization when doing this on a live database. Does anybody have any advice on conditional deletes? Thanks a lot, Best Regards, Chris --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
