mySQL mass update with REPLACE()

A nice shortcut I came across to update content in a mySQL-table without writing a script to do it – which was the first thing that came to my mind…

Case: I had many HTML-snippets in a CMS table called ‘freepages’ containing hard coded domain-names in URLs to images etc. Now guess what happened as we switched the overall site domain from ‘www.mydomain.de’ to ‘info.mydomain.de’? Much of the content seemed to have gone.

This snippet was the elegant way of solving this directly in mySQL without headaches:

UPDATE freepages SET body=REPLACE(body, 'www.mydomain.de', 'info.mydomain.de');

or in case you forgot to switch off magic quotes and have all sorts of noisy data like this “Mary\\\\\’s house” with a random number of backslashes. Use this multiple times:

UPDATE applicant_publication SET title=REPLACE(title, "\\'", "'");
UPDATE applicant_publication SET authors=REPLACE(authors, "\\'", "'");
OR
UPDATE applicant SET research_skills=REPLACE(research_skills, '\\"', '"');

Of course you need one line for each field with that problem.

Sweet!

One thought on “mySQL mass update with REPLACE()

  1. hi,

    danke für den tipp. brauchte diese funktion nun bei der iv und hatte mich an deinen blogeintrag erinnert. hat super geklappt.

    UPDATE iv_item_infos
    SET content = REPLACE(content, ‘Bitte benutzen Sie unser
    Kontaktformular: ‘, ”)
    WHERE infotype = ‘kontaktformular’;

    grüße

    kai

Comments are closed.