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.