I have an stored procedure to delete from a long series of tables. It uses one main ID as parameter. I have to delete a bunch of rows based on a view that show that lot of tables. I want to apply the deleting procedure to every filtered result from that view. Some thing like:

SELECT id FROM view WHERE another_id=100;

And then apply the stored procedure to those results like:

CALL deleteProcedure(id);
  • Where is the "series of tables"? What is a "registry"? What is in the VIEW? Are you asking how to use a CURSOR? – Rick James Apr 10 '15 at 17:32
  • @RickJames I have a table related to like 6 other tables, and some of them have relations to other tables. I made an stored view to show all the results in one single query. By registry i meant row. The view returns a primary key; which i want to send as a parameter to the stored procedure. – jinchuika Apr 10 '15 at 17:41
  • So, the Stored Procedure needs to be 6 (or more) DELETE statements using id? – Rick James Apr 10 '15 at 17:54
  • @RickJames I have already created the stored procedure and it works. But i have to call it for like 150+ rows. I want it to bee called automaticaly. Something like CALL deleteProcedure(SELECT id FROM view WHERE another_id=100 ) – jinchuika Apr 10 '15 at 17:59
  • Why doesn't the stored procedure take the another_id as a parameter? Then it could delete based on a join or subquery instead of performing deletes one ID at a time. – Aaron Bertrand Apr 11 '15 at 16:59

Create another Stored Procedure. In it, DECLARE a CURSOR for SELECT id FROM view WHERE another_id=100;. Create a loop; in the loop FETCH one id, then CALL deleteProcedure(id);. (See the manual and online examples for the details.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.