Multiple table delete

[sheardben]

Hi

I need some help writing a delete query which deletes data from 3 different tables - tried to read up about it but could quite make sense of it.

table dives (main table)
diveid, otherfields, profileid

table profiles
profileid, otherfields

table profile_data (Index on profileid)
dataid, profileid, otherfields

Given a diveid, I want to delete the row from `dives` as well as the corresponding row in `profiles` (dives.profileid = profiles.profileid) along with all the corresponding rows in `profile_data`. (dives.profileid = profile_data.profileid)

Thanks in advance!

[guido2004]

What do you have so far?

[guelphdad]

Look up FOREIGN KEYS, add them to your tables (which will have to be INNODB type) and have deletes CASCADE. then you will only have to remove rows in your first table and the others will delete automatically.

[guelphdad]

look in the manual under delete syntax, there they give an example of multiple table delete. just like a join but you have to mention all tables at the beginning:

DELETE FROM
table1, table2
WHERE table1.somecolumn=table2.somecolumn
AND somecolumn = 'something'

[sheardben]

Thanks, I tried and didn't manage to get it to work, but will read up in the manual and play around with it a bit.

[guelphdad]

why not show us what you tried and we can help you further.

Also you should elaborate. Did you try adding the foreign keys or the multi table delete?