Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Apr 2006
    Posts
    133
    Thanks
    6
    Thanked 0 Times in 0 Posts

    MySQL Stored procedure to delete sub-categories and products

    Hi all,

    I need to delete the sub-categories and products from separate tables by using the ID of the category given.

    Categories table has catID, subcats table also has the catID in common and products table is connected to the subcats table with subcatID field. MySQL database type is MyISAM thats why it doesn't support foreign keys.

    So I need to create a stored procedure to automatically delete the subcats and products of a category.

    Can anybody give me a good example of such stored procedure?

    Many thanks
    telmessos

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,084
    Thanks
    80
    Thanked 4,553 Times in 4,517 Posts
    So you have this table structure?
    Code:
    Table: Categories
        catid int primary key
    
    Table: Subcats
        subcatid int primary key,
        catid int references Categories(catid)
    
    Table: Products
        ...
        subcatid int references Subcats(subcatid)
    ???

    And you want to be able to delete a record from Categories and have it delete all related Subcats and Products records?

    Easy enough:
    Code:
    delimiter //
    
    CREATE PROCEDURE DeleteCategory( del_catid INT )
    BEGIN
        DELETE FROM Products
        WHERE subcatid IN ( 
            SELECT subcatid 
            FROM Subcats 
            WHERE catid = del_catid );
    
        DELETE FROM Subcats
        WHERE catid = del_catid;
    
        DELETE FROM Categories
        WHERE catid = del_catid;
    END
    //
    
    delimiter ;
    You just have to make user you do the DELETEs in "reverse" order, starting with the most dependent.

  • #3
    Regular Coder
    Join Date
    Apr 2006
    Posts
    133
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Many thanks my friend. It works perfectly.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •