CODEWITHSUNDEEP
mysqlarraysstored-procedures
Jack BeNimble
Jack BeNimble

Reputation: 36723

MySql stored procedure - how to select multiple ids into a variable

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?

Upvotes: 0

Views: 427

Answers (1)

Devart
Devart

Reputation: 122040

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.

Upvotes: 1

Related Questions