CODEWITHSUNDEEP
mysqlwordpresswoocommerce
Lachezar Yordanov
Lachezar Yordanov

Reputation: 21

Bulk change WooCommerce products category with query?

First, my MySQL knowledge is basically one sentence. - It is about website DB. Now the situation and what I have done so far. After import went wrong I have about 50k products in the wrong parent category. The child is ok, the correct parent exists and there are some products. Tried manually, plugins, bulk edit plugins it doesn't work or is extremely slow/impossible. I got to the point that i should edit table wp_term_relationships Through reading and copy pasting and little editing i tried the following queries:

UPDATE wp_term_relationships 
SET term_taxonomy_id=2
WHERE term_taxonomy_id=1

UPDATE wp_term_relationships
SET term_taxonomy_id = 
REPLACE (term_taxonomy_id, '1','2')

UPDATE `wp_term_relationships` 
SET `term_taxonomy_id` = 
REPLACE ( term_taxonomy_id, '1', '2' ) 
WHERE `term_taxonomy_id` = '1'

UPDATE wp_term_relationships 
SET term_taxonomy_id = 1 
WHERE term_taxonomy_id = 2

1 is ID of old/wrong category 2 is ID of new/correct category

Everything was done on a different try after backup restore.

simulate goes fine and counts correct amount on go though I always get #1062 - Duplicate entry '18371-855' for key 'PRIMARY'

Any ideas on how to solve this will be appreciated

Additional clarification. These are the columns in the table

object_id - Primary

term_taxonomy_id - Primary - Indexed

term_order

Upvotes: 2

Views: 998

Answers (1)

Rick James
Rick James

Reputation: 142278

REPLACE() is for strings:

REPLACE (term_taxonomy_id, '1','2')

will change 1 to 2, 11, to 22, 10178 to 20278, etc. If you have already done that, you have unrepairably corrupted the data.

This:

UPDATE wp_term_relationships 
SET term_taxonomy_id=2
WHERE term_taxonomy_id=1

updates the one row, changing term_taxonomy_id from 1 to 2. But...

Since term_taxonomy_id is the PRIMARY KEY, it is declared "unique". That is, if you already have a row with term_taxonomy_id = 1 there will be a collision.

I don't know what the taxonomy is about, but I doubt it it has anything to do with parent or child.

Figure out what the "parent" column is and co

UPDATE ... 
SET parent_id=2
WHERE parent_id=1

If this is a hierarchical database, there is no separate table with a many-to-many relationship. So, again, one of us is confused.

Upvotes: 0

Related Questions