6

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".

  • 1
    Are you sure you want an SQL query? Let me put it this way - are you trying to do a one-time migration of old data, or are you looking to implement something programmatically (to work now and in the future)? – TheDeadMedic May 12 '16 at 14:23
  • Good question - thanks for letting me clarify. Looking to do a one-time migration. The second step (which I think I know how to do) is to chop out some of the text in the targeted custom field. – Hal Atkins May 12 '16 at 14:25
0

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 != ''
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.