SQL Query to copy value of a meta_key into another meta_key

Question

I am trying to figure out how to replace the value of one advanced custom field with the value from a different custom field. Would anyone be able to help me structure a SQL query that will do this across the database?

The meta_key that contains the material I am looking to duplicate is "ehp_citation", and the target of the duplication is "ehp_citation".

Answer 1

First take a db backup! Second, in your question you mention the same meta_key, so made the following assumption:

The meta_key you want to keep is "ehp_citation"

The meta_key you want to change is "ehp_citation_old" * so make the correction accordingly

Then you can try something like:

UPDATE `wp_postmeta` AS pm_to_change
LEFT JOIN `wp_postmeta` AS pm_we_want ON pm_to_change.post_id = pm_we_want.post_id
SET pm_to_change.meta_value = pm_we_want.meta_value
WHERE pm_to_change.meta_key = 'ehp_citation_old'
    AND pm_we_want.meta_key = 'ehp_citation'
    AND pm_we_want.meta_value != ''

also make sure you change the db prefix to mach yours.

To explain,

1) we say we want to update the wp_postmeta table and for reference we give it a name: pm_to_change

2) we make an LEFT JOIN to the same table (wp_postmeta) but this time we refer to it by: 'pm_we_want' and also we say that we want the data for the same post_id

3) here we say what to change. we want the meta_value of the pm_to_change to be SET to the meta_value of the pm_we_want

3) Finally we specify from which meta_key we want the above values. So we want the meta_key of the pm_to_change table to be: ehp_citation_old and the meta_key of the pm_we_want table to be: ehp_citation.

Bonus 1: The AND pm_we_want.meta_value != '' in the end also checks if the value we want, from ehp_citation is not empty. Which means that if it is empty, the we keep the old ehp_citation_old

Bonus 2: Run this SELECT first to check that we got the correct data:

SELECT pm_to_change.meta_value AS 'Change_This', pm_we_want.meta_value AS 'To_That'
FROM `wp_postmeta` AS pm_to_change
LEFT JOIN `wp_postmeta` AS pm_we_want ON pm_to_change.post_id = pm_we_want.post_id
WHERE pm_to_change.meta_key = 'ehp_citation_old'
    AND pm_we_want.meta_key = 'ehp_citation'
    AND pm_we_want.meta_value != ''

Answer 2

You could try to do this using Wordpress it's own function. You should consider looking into this: https://codex.wordpress.org/Function_Reference/update_post_meta / https://www.advancedcustomfields.com/resources/update_field/

You could do a code that does the following

• Request all the posts/pages in which the posts that you need are loaded
• Loop through these items using a foreach loop or have_posts() function
• Within the posts, change the values for fields that you want to update.

Note that this is a work-around. It has worked for me many times, hope it works for you too.