there isn’t any way to do this with a single SQL query and you’ll need to do this in PHP so that you can do one query to find what needs to be changes and then loop over these results and do a second query on each row to make the update.
First you need to select the correct fields, these are quick sql examples
‘SELECT * FROM wp_postmeta WHERE meta_value = {$field_key}’
Extract the post ID, and meta_key from the results
// get the correct meta key
$field_meta_key = substr($meta_value, 1)
You will need to loop over these results in PHP and update each row
update the field key reference
UPDATE wp_postmeta WHERE post_id = "$post_id" AND meta_key = "$field_meta_key" SET meta_key = "{$new_meta_key}"
update the field
`
UPDATE wp_postmeta WHERE post_id = “$post_id” AND meta_key = “$meta_key” SET meta_key = “_{$new_meta_key}”
‘
Even this would be prone to causing issues
I would not do this, no one sees the field names and I would live with what I built and learn from the experience to use better naming in the future.
