How can I fix duplicated imported category data?

[icecold]

I received a feed that caused duplicated categories and subcategories ... so I started to fix it by doing SQL queries in the categories TBL such as this:

UPDATE categories
SET parent_id=419 WHERE parent_id in(1245, 1523);

Using that on the offending top categories I was able to fix them, but when I tried to do the same with subcategories that are a parent of other subcats, it didn't work, so my question is, are only top categories the ones in parent_id Column?

If that is the case how can I execute a query such as the above to affect parent subcategories only?

[Paul69]

Although I'm not much with alot of this stuff I have limited sql experience but the data schema looks simple to me. My first load I did was manual w/o easy-populate and I matched the tables my self. I know that sounds crazy but can't you do the same thing off line? Knowing the keys it looks like it may be possible. Another thing I did was export the affected tables and then imported them as I wanted them. I used phpMyAdmin to both import/export the tables and in between I reset the ID's to start back at one. I know this may sound crazy but I was succesful by following the schema and the attributes shown in phpMyAdmin to do it.

Also can't remember which ones but the key in one of the tables was not an auto-number in the database and I had to do it ahead of time in Excel. Still wasn't a problem. Just back up what you already have.

I could be way out on this one but I hope it helps...

[icecold]

Although I'm not much with alot of this stuff I have limited sql experience but the data schema looks simple to me. My first load I did was manual w/o easy-populate and I matched the tables my self. I know that sounds crazy but can't you do the same thing off line? Knowing the keys it looks like it may be possible. Another thing I did was export the affected tables and then imported them as I wanted them. I used phpMyAdmin to both import/export the tables and in between I reset the ID's to start back at one. I know this may sound crazy but I was succesful by following the schema and the attributes shown in phpMyAdmin to do it.

Also can't remember which ones but the key in one of the tables was not an auto-number in the database and I had to do it ahead of time in Excel. Still wasn't a problem. Just back up what you already have.

I could be way out on this one but I hope it helps...

Well yes but that will work if you have a small number of categories and subcategories, I need to fix thousands of rows, because too many products are linked to categories that will no longer exist ... so I am looking for SQL queries that might loop, through them ... my problem is tables are so huge it will take too long to do it manually ...

When I first saw the categories table with parent_id I thought all subcategories were referenced to their parent_id, but that's not the case as I have 4 levels of subcats I am trying to find out how to fix them all at once, or in tandems.

[Paul69]

The master ID is the only one ID right? It is not possible to have multiple ID's linked to a single product right? Maybe I just don't understand but if the subcategories are mapped with the "categories" table and the product relationship key is the ID through the "product_to_categories" then there is only one master ID.

Shoot... Ok I think I see something now, the default in the data base is set to "0" so if there is no parent then "0" is the flag. If it is not "0" then it is a sub category. How about

UPDATE categories
SET categories_id='YOU VALUE' WHERE parent_id ="0"

If you don't have a "0" under parent_id you may have a biffer problem.

[icecold]

The master ID is the only one ID right? It is not possible to have multiple ID's linked to a single product right? Maybe I just don't understand but if the subcategories are mapped with the "categories" table and the product relationship key is the ID through the "product_to_categories" then there is only one master ID.

Shoot... Ok I think I see something now, the default in the data base is set to "0" so if there is no parent then "0" is the flag. If it is not "0" then it is a sub category. How about

UPDATE categories
SET categories_id='YOU VALUE' WHERE parent_id ="0"

If you don't have a "0" under parent_id you may have a biffer problem.

parent_id is not always 0 .... in my understanding parent_id reflects the parent ID of the subcategory for instance if I do a query:

 SELECT *
FROM `categories`
WHERE parent_id =421

I get all the subcats whose parent ID is 421.

422 421 0 2009-04-29 07:39:13 2009-11-23 23:34:19 1 1
Edit Delete 456 421 0 2009-04-29 07:39:14 2009-11-23 23:28:29 1 1
Edit Delete 473 421 0 2009-04-29 07:39:18 2009-11-23 23:34:32 1 1
Edit Delete 486 421 0 2009-04-29 07:39:18 2009-11-23 23:26:47 1 1
Edit Delete 492 421 0 2009-04-29 07:39:18 2009-11-23 23:28:17 1 1
Edit Delete 498 421 0 2009-04-29 07:39:18 2009-11-23 23:33:08 1 1
Edit Delete 537 421 0 2009-04-29 07:39:26 2009-11-23 23:28:46 1 1
Edit Delete 564 421 0 2009-04-29 07:39:26 2009-11-23 23:29:02 1 1
Edit Delete 615 421 0 2009-04-29 07:39:38 2009-11-23 23:34:06 1 1
Edit Delete 707 421 0 2009-04-29 07:39:57 2009-11-23 23:27:35 1 1
Edit Delete 722 421 0 2009-04-29 07:40:06 2009-11-23 23:28:04 1 1
Edit Delete 867 421 0 2009-04-29 07:41:47 2009-11-23 23:29:20 1 1
Edit Delete 869 421 0 2009-04-29 07:41:47 2009-11-23 23:34:44 1 1
Edit Delete 954 421 0 2009-04-29 07:43:53 2009-11-23 23:27:06 1 1
Edit Delete 976 421 0 2009-04-29 07:44:40 2009-11-23 23:33:24 1 1
Edit Delete 1484 421 0 2009-12-07 07:02:39 2009-12-07 07:02:39 1 1
Edit Delete 1523 421 0 2009-12-07 07:05:37 2009-12-07 07:05:37 1 1
Edit Delete 1856 421 0 2010-04-11 07:14:30 2010-04-11 07:14:30 1

but I also get the duplicated categories ... that's what I want to eliminate but also move the subcats under the same folder, and delete the other one, in my case I need to FIX 1523 and move all their subcategories under 615, then delete 1523 ... (there are no linked products under 1523), and so on with the other duplicates ...

[Paul69]

Parent_ID is "0" if it is the top level category though. What I was trying to say is the 1 to 1 relationship represents a single relationship per ID. The top level must have a parent of "0" but yes, everything underneath will have a relationship down the tree. Why can't you replace the parent id to 615 in any place showing a parent of 1523? The relationship is a single so all replacements (parent id) to the other that were 1523 should do it. The only exception is that 615 MUST be either a top level category or have a sub category attached. Maybe I don't understand or I'm just confusing you more. I guess I'm more of a visual person but the logic makes sense to me. Even if you have to reset the table back to all parents (this will also blow out the relationship to product) you can still put it back together at the end. Sounds like I've had way too much coffee and need to try something else now. I hope I'm not making this worse than it is. If I could see the structure it would make sense. Sorry I haven't been much help.

[icecold]

Parent_ID is "0" if it is the top level category though. What I was trying to say is the 1 to 1 relationship represents a single relationship per ID. The top level must have a parent of "0" but yes, everything underneath will have a relationship down the tree. Why can't you replace the parent id to 615 in any place showing a parent of 1523? The relationship is a single so all replacements (parent id) to the other that were 1523 should do it. The only exception is that 615 MUST be either a top level category or have a sub category attached. Maybe I don't understand or I'm just confusing you more. I guess I'm more of a visual person but the logic makes sense to me. Even if you have to reset the table back to all parents (this will also blow out the relationship to product) you can still put it back together at the end. Sounds like I've had way too much coffee and need to try something else now. I hope I'm not making this worse than it is. If I could see the structure it would make sense. Sorry I haven't been much help.

I was exactly doing that browsing manually and executing

UPDATE categories
SET categories_id=cat_Iwant
WHERE categories_id=cat_Iwant_to_remove
OR categories_id=...// (as many categories ID I want to copy ... this is working for subcats)

For instance if I want to remove 3 instances of Blue Rays that have different categories IDs say 456, 1200, 1300 and keep just 456 as categories ID, I do that ...

[icecold]

I was exactly doing that browsing manually and executing

UPDATE categories
SET categories_id=cat_Iwant
WHERE categories_id=cat_Iwant_to_remove
OR categories_id=...// (as many categories ID I want to copy ... this is working for subcats)

For instance if I want to remove 3 instances of Blue Rays that have different categories IDs say 456, 1200, 1300 and keep just 456 as categories ID, I do that ...

This last post is wrong If I do that I will get Duplicate keys MySQL errors the correct way is to go for parent_id instead of category id, and when there are no more subcats and it contains products I do the same but in "products_categories" table instead.

[icecold]

This last post is wrong If I do that I will get Duplicate keys MySQL errors the correct way is to go for parent_id instead of category id, and when there are no more subcats and it contains products I do the same but in "products_categories" table instead.

"products_categories" table meant "products_to_categories" table ...
I couldn't figure out a way to search the db for duplicates and do it automatically ...

I delete categories manually once they are empty ...
Considering the fact there are over 40k products, this is a slow process ... even though the sql commands make it a little faster ...