FreeBSD Administration

MySQL: Search and Replace

There are a number of times when I have had to change one string to another in a MySQL table.  In the past I would do a MySQL dump, edit the file, and then import it back in.  This worked well but it required that either the database was down while I made the change or any updates would be lost while I was editing the dump file (not to mention the time it took). As with most things, there is an easier way to get there.

This command can be used to modify the text in a table, replacing every occurrence of string1 with string2 in columb colname.

UPDATE table SET 'colname' - relace( 'colname', "string1", "string2");

You can also use this with the where command to control what gets changed if required.

Leave a Comment

Your email address will not be published. Required fields are marked *