If you have large amounts of data in MySQL and want to change some values/characters in a particular field, it would be very inconvenient If you must be changed manually. There is MySQL function similar to str_replace in PHP that has a function to change the certain word with other words massively.
MYSQL REPLACE
MySQL REPLACE() replaces all the occurrences of a substring within a string. Perhaps we rarely use this function, but in certain situations, this function will be the savior of developers in data processing.
For example, a colleague of mine who has purchased the azoncastv2 theme. He had the data product with a very large number. Turns out he incorrectly entered the Amazon affiliate ID and asked me how to modify amazon affiliate ID in the table post massively?
By running the following query, we can change the string in the WordPress posts table massively
1 2 3 | UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'your-old-tag-id', 'your-new-tag-id) WHERE meta_value LIKE '%your-old-tag-id%' |
and this one
1 2 3 | UPDATE wp_posts SET post_content = REPLACE(post_content, 'your-old-tag-id', 'your-new-tag-id') WHERE post_content LIKE '%your-old-tag-id%' |
This simple query can be used to change a word in a set of strings rapidly. Thus my brief tutorial on the use REPLACE in MySQL to change a specific string in a set of strings.
Leave a Reply