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!
Table of Contents
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.
Have just fixed broken code tags above – we updated the theme after posting this and something went wrong. Sorry if you landed here a few days ago and found broken stuff!
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.
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`.
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.
Hi Robin,
I was wondering if you could help me…
I am using a plugin which enables me to set a specific meta data to products. The meta key name is fcrm-settings-woo. I need to apply this meta field with different values to all my products. For example, product with ID 3049 has the meta value a:1:{s:19:”purchase_apply_tags”;a:1:{i:0;s:1:”1″;}} and product with ID 3050 has the meta value a:1:{s:19:”purchase_apply_tags”;a:1:{i:0;s:1:”2″;}}.
Unfortunately this meta cannot be exported via the native Woocommerce export function. I can see the values though via MySQL. How can I bulk add this meta data to my products? The meta key name is always fcrm-settings-woo, but the values are always different, as shown above. I was told to use update_post_meta($productId, ‘fcrm-settings-woo’, $settings); but not sure how to get this working…
Thanks!
A useful article for when I want to update fields, but the situation I have is that I can’t be sure every post has the appropriate custom field. I have an input spreadsheet with every post_id and the custom field data where appropriate. I think, but I can’t be sure, that the simplest way to deal with this is first a delete query to remove all relevant custom fields and insert them again. Could you create a variant on your script to insert new custom field data instead of just updating?