Search and replace a term in a MySQL database

Question

I recently added a new project to our issue tracker, which is Redmine. When creating a project, you give it a name and an identifier (which are often the same). There is a note when creating the project that you cannot change the identifier once it has been set. For this reason I was very careful to choose a generic identifier. Unfortunately, I wasn't careful enough and I spelled it wrong! The misspelled identifier appears in the issue tracker URLs. These will be seen by other developers and another company we are working with, so it's a very embarrassing mistake.

So I'm looking for suggestions as to how to fix this. Either Redmine-specific, or something I can do at the database level (which is MySQL).

I've already found a solution that I will probably go with, but I thought it would be worth asking here anyway. I'm hoping someone can offer a simpler solution - maybe a magically SQL one-liner.

The solution I've found is this:

• Dump the database to SQL (using mysqldump)
• search and replace with sed or a text editor
• recreate the database from this SQL.

Thanks for any suggestions.

Answer 1

If the identifier is just confined to some column or set of columns, you can use:

update [table] set [field] = replace([field],'[find]','[replace]');

Replace the bracketed text with the identifiers in your case, as appropriate.

Answer 2

Turns out it was as simple as:

update `projects` set `identifier` = '[NEWNAME]' where `indentifer` = '[OLDNAME]';