Need help with complex find/replace MySQL query

[jonzahlaway]

I have two WordPress tables, one of which has data in it that I would like to add to the other

Table 1:
wp_yoast_indexable

Relevant columns:

• object_id
• object_sub_type
• title
• description

Table 2:
wp_postmeta

Relevant columns:

• meta_key = _yoast_wpseo_title
• meta_value = [not NULL]

&

• meta_key = _yoast_wpseo_metadesc
• meta_value = [not NULL]

What I wish to accomplish:

Replace “title” & “description” in wp_yoast_indexable with the meta_values for “_yoast_wpseo_title” and “_yoast_wpseo_metadesc” in wp_postmeta where:

• “object_sub_type” in wp_yoast_indexable is equal to the word “page”

• “object_id” in wp_yoast_indexable is equal to “post_id” in wp_postmeta

I could stop there, but, if possible, I would also prefer to do this only where:

• title = NULL in wp_yoast_indexable and meta_value = [not NULL] for meta_key “_yoast_wpseo_title” in wp_postmeta

and

• description = NULL in wp_yoast_indexable and meta_value = [not NULL] for meta_key “_yoast_wpseo_title” in wp_postmeta

I have no idea if this is even possible, but hopefully I’ve explained it well. Any help forming the correctly formatted query would be greatly appreciated.

[r937]

you’ll need two updates statements, one for each column

UPDATE wp_yoast_indexable
INNER
  JOIN wp_postmeta
    ON wp_postmeta.post_id = wp_yoast_indexable.object_id
   AND wp_postmeta.meta_key = '_yoast_wpseo_title'
   AND wp_postmeta.meta_value IS NOT NULL
   SET wp_yoast_indexable.title = wp_postmeta.meta_value
 WHERE wp_yoast_indexable.object_sub_type = 'page'    
   AND wp_yoast_indexable.title IS NULL

UPDATE wp_yoast_indexable
INNER
  JOIN wp_postmeta
    ON wp_postmeta.post_id = wp_yoast_indexable.object_id
   AND wp_postmeta.meta_key = '_yoast_wpseo_metadesc'
   AND wp_postmeta.meta_value IS NOT NULL
   SET wp_yoast_indexable.description = wp_postmeta.meta_value
 WHERE wp_yoast_indexable.object_sub_type = 'page'  
   AND wp_yoast_indexable.description IS NULL

[jonzahlaway]

This worked perfectly. Thank you so much.

[r937]

you’re welcome