...

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



telmessos
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
telmessos

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 )
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.

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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum