SQL: How to copy the value of a meta_key into an array in another meta_key?

Question

Asking here because it seems more a matter of SQL than Wordpress.

I have a Wordpress installation that uses custom fields from both the ACF plugin and the template. There are over a thousand posts using the plugin’s "Subtitle" field - the template fields are empty.

Now I need to migrate all the values of these fields to the fields of the template itself, called "td_subtitle". I saw an answer on how to do this with SQL:

update wp_postmeta set meta_value = 'subtitle' where meta_key = 'td_subtitle';

But checking the database, the value of "td_subtitle" is inside an array, like this:

a:1:{s:11:"td_subtitle";s:24:"Subtítulo de um post blablabla";}

"td_subtitle" actually goes inside meta_key "td_post_theme_settings", which has other values besides it.

Question: how to move meta_key values from Subtitle to td_post_theme_settings > td_subtitle?

Answer 1

One way to do this, perhaps not the most efficient, but one that would meet the demand, would be..

First of all, have backup of everything!

add a function to the file functions.php of its theme, which will be triggered as soon as the first access to the site is carried out, restricting to the admin user (at least). It is possible to add other checks to avoid duplicities and undue access.

In this function, you can go through the posts, and using the function $old_value = get_post_meta( $post_id, 'subtitle' ) take the old data and with the function update_post_meta( $post_id, 'td_subtitle', $old_value ) save in the meta correct.

This, considering that these two functions are sufficient to access and save the data you need to manipulate.