mysql - sql find and replace -

so i'm pretty systems admin , can manage mysql servers no issue. problem in coding sql, , more coding sql feel safe automate in nightly scripts. thats awesome stack overflow guys come in.

i have production wordpress site sync dev server have close live testing on before push production. right after update mysql copy of production need find , replace 2,000 strings.

all need find way execute sql find , replace http://drunkonjudgement.com http://dev.drunkonjudgement.com place in table.

help me obi wan kenobis hope.

you want this

update table_name set column_name = replace(column_name, 'http://dev.drunkonjudgement.com', 'http://drunkonjudgement.com');

this ensure replace text looking in specific column text want without changing text around it.

so example shorten like: replace(column_name, 'dev.drunkonjudgment.com', 'drunkonjudgment.com')

you can specify clause replace items contain text this:

where column_name '%dev.drunkonjudgement.com%'

ok columns in tables, search entire db. can use statement this:

select concat('update ', table_name, ' set ', column_name, ' = replace(', column_name, ',''dev.drunkonjudgment.com'',''drunkonjudgment.com'')', ' ', column_name, ' ''%dev.drunkonjudgment.com%''' ) information_schema.columns 

it output sql statement 1 above each column , table in database , because using replace statement if not find text not replace ensure update records contain text.

so automate use cursor, have not tested following code this:

  declare done boolean default 0;    declare sql varchar(2000);     declare cmds cursor       select concat('update ', table_name, ' set ', column_name, ' = replace(', column_name, ',''dev.drunkonjudgment.com'',''drunkonjudgment.com'')', ' ', column_name, ' ''%dev.drunkonjudgment.com%''' ) information_schema.column;     declare continue handler sqlstate '02000' set done=1;    open cmds;    repeat       fetch cmds sql;       prepare stmt sql;       execute stmt;       drop prepare stmt;    until done end repeat;    close cmds;