Jump to content

Delete hierarchical parent and child category and update child as parent


thara
 Share

Recommended Posts

Data from category table:

+-------------+-----------+---------------------+-------------+
| category_id | parent_id | name                | description |
+-------------+-----------+---------------------+-------------+
|           1 |      NULL | Products            | NULL        |
|           2 |         1 | Computers           | NULL        |
|           3 |         2 | Laptops             | NULL        |
|           4 |         2 | Desktop Computers   | NULL        |
|           5 |         2 | Tab PCs             | NULL        |
|           6 |         2 | CRT Monitors        | NULL        |
|           7 |         2 | LCD Monitors        | NULL        |
|           8 |         2 | LED Monitors        | NULL        |
|           9 |         1 | Mobile Phones       | NULL        |
|          10 |         9 | LG Phone            | NULL        |
|          11 |         9 | Anroid Phone        | NULL        |
|          12 |         9 | Windows Mobile      | NULL        |
|          13 |         9 | iPad                | NULL        |
|          14 |         9 | Samsung Galaxy      | NULL        |
|          15 |         1 | Digital Cameras     | NULL        |
|          16 |         1 | Printers and Toners | NULL        |
|          17 |        14 | Galaxy S Series     | NULL        |
|          18 |        14 | Galaxy Note Series  | NULL        |
|          19 |        14 | Galaxy Z Fold2 5G   | NULL        |
|          20 |        17 | Phone 1             | NULL        |
|          21 |        17 | Phone 2             | NULL        |
+-------------+-----------+---------------------+-------------+

Just think, I hava an array of category ids for delete like this:

ids = [9,17,20];

Now I want to delete the category related to the above array and update the relevant child category. According to this example, the parent_id of the category_id 10,11,12,13,14 should be 1 The parent_id in category 21 should be updated to 14.

In another case, suppose I delete category 9, 18 then all the relevant sub and sub sub categories should be updated as parant category.

I hope somebody may help me out.

Thank you

Link to comment
Share on other sites

Yes sir its working... Thank you.

One thing, I really want to see if the products related to this deleted category_id are in the product table and if so, set the category_id of those products to a general category. In this example, it is category number 1.

Sir, Is there any possible way to modify this single query to reset the category_id in the product table also?

Thanks again.

Link to comment
Share on other sites

Posted (edited)

I don't see why that should be necessary.

If your product were in in category 16 before the update it remains in catgory 16. It's just that 16 has moved a different parent, but the product remains unchanged..

Edited by Barand
Link to comment
Share on other sites


Sir, lets assume, I will delete category 16 from the category table, according to my table it is a parent category. If there are two products belonging to that category in the product table, then what happens to the two category ids in that product, the category corresponding to that category id is not in the category table because it has already been deleted from the category table. Isn't that right?

Link to comment
Share on other sites

In that case you have broken referential integrity rules by removing the products' category

Options:

  • Change the category of those products to a new one before deleting category 16.
  • Don't allow the category deletion in the first place
  • Delete the products too, as that category of item is no longer sold.
  • Set the category of those products to NULL to show they are now orphans

Those lst 3 options can be set in the foreign key cascade setting.

Link to comment
Share on other sites

Sir, Thanks for response

Quote

Change the category of those products to a new one before deleting category 16.

I was thinking about your first option and setting procut category to a new common one. Sir is there a way to do this by modifing above query ?

Thank you.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.