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!
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.
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'
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.
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?