-1

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.

  1. The update query should be case sensitive (do not replace uppercase occurences)
  2. Replace all occurrences within a meta key that means frank_oldprefix_foo becomes frank_newprefix_foo
Blackbam
  • 12,200
  • 19
  • 71
  • 117
  • 1
    Is it a prefix (as in your example) (so the old string always *starts* with that substring) or does it appear anywhere in your string (as `%wp_%` would search for) and/or is it the first occurence, but anywhere in your string? Or to put it in some examples: what would `blackbam_WP_wp_blackbam` and `WP_blackbam` be replaced by? (Uppercase is used intentionally) – Solarflare Jun 25 '18 at 11:47
  • Ok I should clarify this: Lets assume that we do not user uppercase names for our tables and therefore make it case sensitive. Lets also assume that the prefix can occur anywhere within the name string and use all of them. I will update the question. – Blackbam Jun 25 '18 at 12:52
  • 1
    Well, in that case have a look at e.g. [MySql - Way to update portion of a string?](https://stackoverflow.com/q/1876762/6248528), [MySQL string replace](https://stackoverflow.com/q/5956993/6248528), and/or the MySQL doc for [replace](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_replace) (unless I missed something special about your situation). – Solarflare Jun 25 '18 at 14:37
  • Do you really have to live in a single `DATABASE`? It is possible to reference tables across databases. – Rick James Jun 25 '18 at 23:33

1 Answers1

2

Use the Mysql replace as solarflare suggested. It is case sensitive (I just double checked on my test multisite). So the sql would be:

Usermeta:

UPDATE newprefix_usermeta SET meta_key=REPLACE(meta_key,'oldprefix','newprefix') WHERE meta_key like 'oldprefix%';

Options:

UPDATE newprefix_options SET option_name=REPLACE(option_name,'oldprefix','newprefix') WHERE option_name like 'oldprefix%' ;
Blackbam
  • 12,200
  • 19
  • 71
  • 117
anmari
  • 3,242
  • 1
  • 12
  • 12