mysql cascade delete

[Red Squirrel]

Say I have two tables of this format:

table1:
index,
field1,
data,

table2:
field1,
field2,


Each row in table1 has a row in table2 where field1 of table2 is equal to field1 in table1.

How do I go about deleting every entry in table1 where the index field is of NN value, but also delete the corresponding entries in table2?

I tried googling delete on cascade as I used that way back, but all I"m getting is people saying to add that when defining the table. This is not a table I have defined, I need to do it on the fly during the delete statement. Is there perhaps a way with sub queries I could do it?

 

[Crusty]

Well, they are right. If you want true cascading deletes/updates you need to adopt InnoDB for it's foreign key support.. otherwise you just have to do it in your code/query logic.

You can do it with one query and it would look something like DELETE table2 FROM table2 INNER JOIN table1 ON table1.field1 = table2.field1 WHERE table1.index = NN

but it might be easier to visualize if you just break it up into multiple queries and use your code behind for the logic of the cascading.

SELECT table2.field1 FROM table2 INNER JOIN table1 ON table1.field1 = table2.field1 WHERE table1.index = NN

Then just loop your result set calling a DELETE * FROM table2 WHERE field1 = ResultSet[Counter]

Obviously the single DELETE query will be more efficient, but I guess it kind of depends on context.

btw.. all of this can be found in mysql's help documents

http://dev.mysql.com/doc/refman/5.0/en/delete.html

 

[Red Squirrel]

I ended up using a loop, not exactly the best way as I'm dynamically generating queries (could be 10 queries, or 1000, etc) but in my case there will never be that many entries.

phpbb's structure is kinda, not exactly the most efficient to work with. They seperated posts and posts text so when you want to delete a bunch of posts you also have to go and delete all the posts_text entries which don't refer to the original topic or forum id.

Did not know you could do a join delete though, I'll note that for next time I run into this. I tried to do a basic multi table delete the same way you'd do a select from two tables but that did not work.