Delete hierarchical parent and child category and update child as parent

[thara]

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

[Barand]

try

UPDATE category a 
       JOIN category b ON a.parent_id = b.category_id
SET a.parent_id = b.parent_id
WHERE a.parent_id = 9;

DELETE FROM category WHERE category_id = 9;

(Moving to MySQL forum)

[thara]

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.

[Barand]

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 May 30, 2021 by Barand

[thara]

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?

[Barand]

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.

[thara]

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.

[Barand]

Do you mean like this

UPDATE category a 
       JOIN category b ON a.parent = b.id
       JOIN product p ON p.category = a.id
SET a.parent = b.parent
  , p.category = b.parent
WHERE a.parent = ?;

 

[thara]

Yes Sir, this is what I needed. Thank you