CODEWITHSUNDEEP
mysqlsql
Sunny Soni
Sunny Soni

Reputation: 182

The best way to delete items in hierarchy way from a DB table in mysql?

I have a database table of categories. Two of its columns are 'category_id' and 'parent_category' which i am using for deletion . The values of these columns are aligned as :

enter image description here

e.g. if i am deleting 'category_id' 10 then all its children(to any depth) 11,12,13 should also be deleted. please suggest me the best mysql query.

thanks in advance.

Upvotes: 2

Views: 281

Answers (2)

Zemistr
Zemistr

Reputation: 1049

If you do not want to use foreign keys, so you can add a column parent_path

ALTER TABLE `table`
ADD `parent_path` varchar(255) NOT NULL DEFAULT ''
AFTER `parent_category`, COMMENT='';

Now create a procedure that creates the parent_path

DELIMITER $$
DROP PROCEDURE IF EXISTS UpdateParentPath$$

CREATE PROCEDURE UpdateParentPath()
    BEGIN
        DECLARE count INT;
        SET count = 1;

        WHILE count > 0 DO
            UPDATE `table` as `t`
                LEFT JOIN `table` as `p` ON `t`.`parent_category` = `p`.`category_id`
            SET `t`.`parent_path` = CONCAT(`p`.`parent_path`, IF(`p`.`parent_path` = '', '', ','), `p`.`category_id`);

            SELECT ROW_COUNT() INTO count;
        END WHILE;
    END$$
DELIMITER ;

Call this procedure

CALL UpdateParentPath();

And now ... finally ... you start deleting

DELETE FROM `table` WHERE `parent_category` = '9' OR `parent_path` LIKE '%,9,%'

Before every deleting or after insert/update is necessary call UpdateParentPath procedure.

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 157136

The easiest way seems to be this:

  1. Create a foreign key constraint between the parent_category and category_id column;

  2. Set cascade delete on.

    create table yourTable
( ...
  add constraint
  foreign key (parent_category)
  references yourTable(category_id)
  on delete cascade
)

This will solve your problem totally without effort.

Upvotes: 3

Related Questions