MYSQL: I want to update all rows in a table with values from another table where values from the first table are equal to the second

I am trying to update a damaged WordPress terms relation (in tables wp_terms and wp_term_relationships).

Some tags were entered in WordPress with their “term_id” number instead of their “name”, so instead of creating a relation with the original correct tag, a new tag was created with this number as its name.

(For instance, if tag “toy” had term_id -> 2010, the new tag was named as “2010” with a new random term_id, lets say 30453).

In the above table, the correct relation for both objects (3456 & 4362) is with “toy” (with term_id 2010), but the error created a new term (“2010”) with another term_id (30453). We want both object_ids to be related with 2010 as below:

Trying to repair the relationships and relate the object_id to the correct term_id, I tried the following SQL query:

UPDATE wp_term_relationships JOIN wp_terms SET wp_term_relationships.term_taxonomy_id = wp_terms.name WHERE wp_terms.name REGEXP '^[0-9]*$' AND wp_term_relationships.term_taxonomy_id = CAST(wp_terms.name as INTEGER);

(I am CASTing the wp_terms.name as INTEGER to avoid type mismatch, though I don’t know if it is necessary.)

This query runs without error BUT returns “0 lines affected” and updates no data.

Can anyone explain why and propose a working solution?

Answer

Use a proper join with an ON clause, on the correct columns like this:

UPDATE wppp_term_relationships tr
JOIN wppp_terms t ON tr.term_taxonomy_id =  t.term_id
SET tr.term_taxonomy_id = t.name 
WHERE t.name REGEXP '^[0-9]*$';

See the demo.

Advertisement

Answer