View Full Version : MySQL Stored procedure to delete sub-categories and products

07-25-2011, 09:13 PM
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

Old Pedant
07-26-2011, 12:36 AM
So you have this table structure?

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:

delimiter //

CREATE PROCEDURE DeleteCategory( del_catid INT )
WHERE subcatid IN (
SELECT subcatid
FROM Subcats
WHERE catid = del_catid );

WHERE catid = del_catid;

DELETE FROM Categories
WHERE catid = del_catid;

delimiter ;

You just have to make user you do the DELETEs in "reverse" order, starting with the most dependent.

07-26-2011, 08:38 PM
Many thanks my friend. It works perfectly.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum