I'd like to modify a stored procedure to delete "child" rows when a "master" row is deleted. This means I need to create a query which selects all the child row ids into an array variable, then iterate over the array and delete the related rows. Or, if it's not possible to do it that way, then iterate over a loop and delete each record, whichever way doesn't matter. How to do this?
Add a comment
|
1 Answer
If you have a deal with foreign keys, then add ON DELETE CASCADE option. Using FOREIGN KEY Constraints
From the documentation - CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported.
Also, you can use DELETE statement to remove records from several tables - DELETE Syntax
About arrays - MySQL does not have this type. So, you should think about workaround, for example, you can create additional table, fill it with ID values, and then use it other queries.
1 Comment
Jack BeNimble
Thanks. I ended up just using the DELETE syntax. i.e. DELETE tb1 FROM tb1,tb2 WHERE tb1.id= tb2.id;