MYSQL updating fields with linked data

2 Views

I have two databases, and I need to link their data together and am unsure of how to proceed.

I have two slightly modified WordPress tables (just for the purpose of migrating data)

Read More
wp_term_taxonomy
++++++++
term_taxonomy_id
term_id
description
parent
count

wp_terms
++++++++
term_id
name
slug
term_group
old_parent
old_id

In wp_terms, I added the old_parent and old_id fields, as that is the data coming from my old database.

I need to UPDATE the wp_term_taxonomy table, specifically the parent field with data from wp_terms table. Specifically, anywhere where wp_terms.old_parent has a record, I need to find the corresponding wp_terms.old_id field (so, if there is a parent of 1, it needs to go back through and look for old_id = 1), and then update wp_term_taxonomy.parent with the value of wp_terms.term_id for the matched wp_terms.old_parent.

I’ve tried a few variations of something like this, but I am obviously missing something fundamental: 

UPDATE wp_o1y4a6ifud_term_taxonomy AS a, wp_o1y4a6ifud_terms AS b, wp_o1y4a6ifud_terms AS c
SET a.parent = b.term_id
WHERE b.old_parent = c.old_id
Post Views: 2

Related posts

1 comment

  1. Here is my approach:

    UPDATE wp_o1y4a6ifud_term_taxonomy AS a
INNER JOIN  wp_o1y4a6ifud_terms AS b
ON  a.term_id = b.term_id 
INNER JOIN  wp_o1y4a6ifud_terms AS c
ON b.old_parent = c.old_id
SET a.parent = c.term_id

Comments are closed.