I am trying to update a series of products that when entering them in the WooCommerce store, some labels are wrong. As it must be a massive change, I wanted to do it for MySQL
.
First, I have removed the products that correspond to the wrong pattern:
SELECT a.ID, a.post_name, b.term_id, b.name, c.object_id,c.term_taxonomy_id
FROM wp_strposts a, wp_strterms b, wp_strterm_relationships c
WHERE c.object_id = a.ID and c.term_taxonomy_id = b.term_id and a.post_name like '%plata%' and b.term_id = 41
ORDER BY a.ID desc;
This returns me those products that have the word silver in their name, but have been assigned an erroneous label of gold:
The idea is to change the field term_taxonomy_id
whose value is 41 (gold label) to 52 (silver label).
I have prepared the following statement:
update wp_strterm_relationships set term_taxonomy_id = 52 where object_id in (1234,
1232,
1231,
1226,
1222,
1196,
1189,
1184,
1182,
1180,
1177,
1166,
1160,
1152);
But when I run it I get the following error:
#1062 - Entrada duplicada '1152-52' para la clave 'PRIMARY'
As much as I look, I don't understand what primary key error is giving me. Any ideas?
The error indicates that it already exists in
wp_strterm_relationships
a record withobject_id
=1152 andterm_taxonomy_id
=52 (apart from the record you show withterm_taxonomy_id
=41), which indicates that this element already had the 'gold' tag assigned as well.In this case of duplicate the UPDATE is not necessary, but simply DELETE.
For the query to run without a problem, your list of ids must discard those that already have
term_taxonomy_id
=52 (more than one label is allowed).You can run the UPDATE:
And finally remove the silver tags where they have both:
Finally, managing taxonomies can be done using code with the functions
wp_set_object_terms()
andwp_remove_object_terms()
https://developer.wordpress.org/reference/functions/wp_set_object_terms/ https://developer.wordpress.org/reference/functions/wp_remove_object_terms/
I have found the problem doing a manual update by
phpMyAdmin
.The sentence
UPDATE
that I publish in my question is incomplete or poorly formulated, that's why it gives the error. When I'm telling it to update the tag for all those IDs, I hadn't added inWHERE
that the field to look for has the value 41 (for gold).This statement does work:
You could have used the
SELECT
verification query itself by doing aJOIN
This query would update the a column
term_taxonomy_id
for52
all matches of whatever you put in the sub-query.NOTICE: Before bulk updates, make a backup of the table(s) you are going to update.