WordPress – Using MySQL to Update Specific Custom Fields

Some time ago, we told you how to Find & Replace Data using MySQL – today I am going to explain how to use this query to update specific custom fields in WordPress databases, in bulk, on-the-fly, without having to login, open, and edit every single WordPress post you’ve got!

Remember, before doing anything to your database, backup so you can restore if you mess things up. It is your responsibility!

Find & Replace in MySQL

To revisit the query, this is how to find & replace data in MySQL:
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');

Applying This to WordPress Custom Fields

All well and good, but how do we apply this to WordPress post custom fields? Well, the below queries will allow you to update various elements of WordPress Custom Fields. In each query, we are going to assume that your WordPress database prefix is “wp_” – alter the string to suit your needs.

For Custom Fields, the table you want to be toggling is the “wp_postmeta” table. Using a program like PHPMyAdmin, click on that table, then hit “SQL” at the top to run queries on it.

Update Custom Field Name

update  set 'wp_postmeta' = replace('meta_key','[OLDKEYNAME]','[NEWKEYNAME]');

Update Custom Field Value

update  set 'wp_postmeta' = replace('meta_value','[OLDVALUENAME]','[NEWVALUENAME]');

Update Custom Field Value For Specific Custom Fields

Maybe you want to replace the number “10” with the number “11” but only on the custom fields where the custom field name is “price” – below is the query you’d use for that:
update  set 'wp_postmeta' = replace('meta_value','[OLDVALUENAME]','[NEWVALUENAME]') WHERE 'meta_key' = 'price';
The above query is really powerful, and can allow you to update many specific custom fields really quickly and easily, for example if you have a custom field referring to the URL of thumbnails, and you recently moved your domain!

Want Other Custom Field MySQL Queries?

Leave a public comment below and I will try to update the above list.

Want us to Manage or Host your WordPress Website?

Please contact us for a no obligation conversation and quotation.

7 thoughts on “WordPress – Using MySQL to Update Specific Custom Fields”

    • FYI your syntax is completely wrong here. I needed to update a custom field value for specific custom field but this was completely off the mark. Every cloud and all that as I just figured it out for myself, but it maybe worth you updating your post so others can benefit from it – and use the same logic for your other queries noted.

      update wp_postmeta
      set meta_value = VALUE
      where meta_key = ‘CUSTOM_FIELD_NAME’;

      • Hi Tony,

        Your query does something different – mine replaces value x with value y only on custom field z. Yours Sets all values in custom field z to value x. Which is fine, if this is what your query is intended to do! The syntax is not, therefore, wrong, but rather different!

        But if it helped point you to the query you needed, then great. Its mainly logic and language. As with most programming. Until you find the counter intuitive bits, of course.

  1. I don’t know what MySQL version you were using, but the replace command that works for me is

    UPDATE wp_postmeta SET `meta_value` = REPLACE( `meta_value` , ‘[OLDVALUENAME]’, ‘[NEWVALUENAME]’ ) WHERE `meta_key` LIKE ‘[META_KEYNAME]’

    with the square brackets removed, of course, and necessary names added in the 3 spots

    • Hi Ann, its possible this is caused by the actual data here, but whatever works to get this done. This type of query is one to have stored away for a time when its needed! I don’t think MySQL version would have a major impact on this, though it might be (something like) a trailing space in the relevant `meta_key`.

  2. Also kind of wrong for me.
    The one of Ann is working :
    UPDATE `table_name`
    SET `meta_value` = REPLACE( `meta_value` , ‘old_value’, new_value’ )
    WHERE `meta_key` = ‘meta_keY_name’

    • “LIKE” and “=” do slightly different, but similar, things. = (equals) is for EXACT MATCH. LIKE… as the name suggests, allows “%some string%” where “%” is wildcard. Also, a trailing space will come up “true” with “LIKE” but FALSE with “=”.

      As a consequence, your query will work, yes, but the one you’ve written is different from Ann’s which uses LIKE in the WHERE clause.


Leave a comment