mySQL mass update with REPLACE()

Snippets, mySQL October 26th, 2007

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');

Sweet!


Leave a comment Comment RSS

Previous: Sync structure of 2 mySQL databases
Next: Soccer skills in the office

Comments to “mySQL mass update with REPLACE()”:

  1. Kai | April 17th, 2008 at 0:00

    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