How can I search and replace a string from an undetermined number of columns and tables using MySQL?

Question

Using purely MySQL, I want to search and replace 'oldString' with 'newString' from and undetermined number of tables and columns. A search for my table prefix in information_schema, reveals all the tables that I need to look for.

I have the following trials: Querying DB table prefix on information_schema

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM information_schema.TABLES WHERE table_name LIKE  '%mysite_www%'

Results:

+-----------------+----------------------------+
| TABLE_SCHEMA    | TABLE_NAME                 |
+-----------------+----------------------------+
| myDB            | mysite_www_moredata        |
| myDB            | mysite_www_data            |
| myDB            | mysite_www_urls            |
| myDB            | mysite_www_pages           |
| myDB            | mysite_www_feedback        |
| myDB            | mysite_www_comments        |
| myDB            | mysite_www_links           |
+-----------------+----------------------------+
Query results yields about 200 tables or so.

I want to take the results, filter it for a particular string and replace it with a new one. Replace 'oldString' with 'newString'.

For each TABLE_NAME, search for any column WHERE column LIKE '%oldString%'.

WHERE CONCAT(table1, table2, ... tableN) LIKE 'oldString';

For each column result, update 'oldString' to 'newString'.

UPDATE tableN SET columnN = REPLACE (columnN, '%oldString%', 'newString') WHERE URL LIKE '%oldString%';

I need to do this in pure MySQL as it will be a store procedure. Any assistance or tips is greatly appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 10:25

So by using information_Schema you can query both the tables and columns to get a result. I have done something similar in this SO answer.

Essentially query the information_schema and let MySQL construct the SQL for you. The above link should get you going.

UPDATE:

You can use the following query to construct all the Update statements

SELECT CONCAT('UPDATE ',TABLENAME, 'SET ', COLUMN_NAME,'= REPLACE (',COLUMN_NAME, '''%oldString%'', ''newString'') WHERE URL LIKE ''%oldString%''')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE  '%somecriteria%' AND COLUMN_NAME LIKE '%somecriteria%'

Insert these resulting string into a temp table then loop through the rows executing the string by using the technique described here

Thank you, sorting through the information_Schema was part of the answer. — JerryHuang.me, Sep 11 '13 at 21:08

How can I search and replace a string from an undetermined number of columns and tables using MySQL?

1 Answers1