1

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);
5
  • 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
    Commented Apr 10, 2015 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
    Commented Apr 10, 2015 at 17:41
  • So, the Stored Procedure needs to be 6 (or more) DELETE statements using id?
    – Rick James
    Commented Apr 10, 2015 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
    Commented Apr 10, 2015 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. Commented Apr 11, 2015 at 16:59

1 Answer 1

1

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 and acknowledge you have read our privacy policy.

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