Find and replace sql

Question

So, I am a pretty good system administrator and can easily manage mysql servers. My problem is that in fact sql coding and, more specifically, sql coding, I feel safe to automate in nightly scenarios. Thats where you amazing guys come in.

I currently have my version of the WordPress site for my dev server, so we should be close to testing before starting production. Immediately after I upgrade mysql to be a copy of production, I need to find and replace about 2,000 rows.

All I have to do is find a way to execute SQL to find and replace http://DrunkOnJudgement.com with http://dev.DrunkOnJudgement.com anywhere in any table.

Help me, Obi, Van Kenobis, my only hope.

Answer 1

Do you want to do something like this

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

this will allow you to simply replace the text you are looking for in a specific column with the text that you want it to not change the text around it.

, , - : replace (column_name, 'dev.DrunkOnJudgment.com', 'DrunkOnJudgment.com')

where, , , :

column_name '% dev.DrunkOnJudgement.com%'

- , db. :

SELECT Concat('UPDATE ', TABLE_NAME, ' SET ', COLUMN_NAME, ' = REPLACE(', COLUMN_NAME, ',''dev.DrunkOnJudgment.com'',''DrunkOnJudgment.com'')', ' WHERE ', COLUMN_NAME, ' like ''%dev.DrunkOnJudgment.com%''' ) FROM INFORMATION_SCHEMA.COLUMNS

sql, , replace, , , , , .

, , , :

  DECLARE done BOOLEAN DEFAULT 0;
   DECLARE sql VARCHAR(2000);

   DECLARE cmds CURSOR
   FOR
   SELECT Concat('UPDATE ', TABLE_NAME, ' SET ', COLUMN_NAME, ' = REPLACE(', COLUMN_NAME, ',''dev.DrunkOnJudgment.com'',''DrunkOnJudgment.com'')', ' WHERE ', COLUMN_NAME, ' like ''%dev.DrunkOnJudgment.com%''' ) FROM INFORMATION_SCHEMA.COLUMN;

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
   OPEN cmds;
   REPEAT
      FETCH cmds INTO sql;
      PREPARE stmt FROM sql;
      EXECUTE stmt;
      DROP PREPARE stmt;
   UNTIL done END REPEAT;
   CLOSE cmds;

Answer 2

, update where?

update myTable
set myCol = 'http://dev.DrunkOnJudgement.com'
where myCol = 'http://DrunkOnJudgement.com'

Answer 3

At this point in the product life cycle, I think it's too late to propose developing a solution that completely fixes this problem? For example, not using absolute URLs or replacing the URLs with some variable that evaluates at runtime?