query to replace term in all tables of database

Question

I want to search for http://example.com and replace with https://example.com.

I know I can target a specific table and column with this approach:

UPDATE table_name SET post_content = REPLACE(column_name, 'http://example.com', 'https://example.com');

But how do I run a query which targets all tables/columns: the entire database?

Possible duplicate of [Search & replace 'http' to 'https' in database](http://stackoverflow.com/questions/42857921/search-replace-http-to-https-in-database) — Bernd Buffen, Mar 17 '17 at 16:55

score 0 · Answer 1 · answered Mar 17 '17 at 16:30

0

Do a DB dump and open it as a text file. Find and replace. Save and then re-import.

As far as I know, I don't think you can use REPLACE on all tables in one query.

answered Mar 17 '17 at 16:30

NiallFH

192
9

score 0 · Answer 2 · answered Mar 17 '17 at 16:35

There a two ways to do it. The first is to create SQL UPDATE via the information_schema and execute it as prepared statement. this is much work.

you must look at each column if you can do a replace, so you must ignore INTS and ENUMs etc.

The second way is not a real SQL change, but it works: Generate a full SQL-Dump from your database and make the changes in this file via editor or via commandline with AWK or SED. After this you can import the changed file

query to replace term in all tables of database

2 Answers2