WordPress is saving a lot of information on users and settings including the table name. This is quite annoying if you have to change your WordPress table prefixes (which might be necessary if two WordPress installations are using the same database for example).
You have to update a lot of keys in the wp_options
table which are gathered like this:
SELECT * FROM `myprefix_options` WHERE `option_name` LIKE '%wp_%'
You also have to update a lot of keys in the wp_usermeta
table which are gathered like this:
SELECT * FROM `myprefix_usermeta` WHERE `meta_key` LIKE '%wp_%'
So the queries presented here will deliver the given result. Now I am searching for the following advanced query:
For each result in (result set of select query) replace the value of meta_key with the new prefix meta key.
Example:
[umeta_id][user_id][meta_key][meta_value]
1|1|oldprefix_foo|whatever
2|2|oldprefix_bar|abc
Should become:
[umeta_id][user_id][meta_key][meta_value]
1|1|newprefix_foo|whatever
2|2|newprefix_bar|abc
How can I achieve this (in a single) MySQL query?
EDIT: According to the input of @Solarflare we assume the following.
- The update query should be case sensitive (do not replace uppercase occurences)
- Replace all occurrences within a meta key that means frank_oldprefix_foo becomes frank_newprefix_foo