MySQL Forums
Forum List  »  Newbie

Multiple table delete
Posted by: Nik Holmes
Date: August 22, 2005 09:35AM

Hi -

I have several tables with cross-references to another table's ID, eg I have a table called 'users' in which each user has an ID, and I have a table of 'cars' and a table of 'children', each row of which has a 'userID' column. A particular userID might have multiple entries in both cars and children.

When I delete a user, I also want to delete all the entries in 'cars' and 'children' which have that userID. I read the docs which say I can do multiple-table deletes (I'm using mysql 4.1), but all the examples use joins. AFAICS I don't need any joins, I just want to replace ...

DELETE FROM cars WHERE userID=n;
DELETE FROM children WHERE userID=n;

...with a single query which deletes entries from both tables. I tried

DELETE FROM cars, children WHERE cars.userID=n OR children.userID=n;

but that was rejected as incorrect syntax.

Please can anyone tell me the right syntax for this simple query? Or is it just as fast in mysql to use two delete queries?

Thanks
Nik

Options: ReplyQuote


Subject
Written By
Posted
Multiple table delete
August 22, 2005 09:35AM
August 22, 2005 12:21PM
August 22, 2005 04:17PM
August 22, 2005 04:53PM
August 23, 2005 03:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.