0

I'm looking to replace all occurrences of characters in all columns of all tables in my database.

I got the name of my column like this:

select table_name, column_name from information_schema.columns;

And I would apply an UPDATE REPLACE like this:

update table_name set column_name = replace (column_name, "a", "A");

PS : The replacement of "a" to "A" is just one example, my problem is rather how to link table_name and column_name between my two queries.

I tried with subselect, like:

update (select table_name from information_schema.tables as tables) set (select column_name from columns as information_schema.columns Where table_name = tables) = replace (columns, "a", "A");

But I still get errors when I try. What is the right way to do this?

Thank you in advance.

user2420469
  • 91
  • 3
  • 14

2 Answers2

2

What you need is a stored procedure that will use the information_schema to find all tables and columns within your database, and execute an update statement for all these tables.

Have a look at the following question which answers exactly what you wish to do: Find and replace in entire mysql database

Community
  • 1
  • 1
Menelaos
  • 23,508
  • 18
  • 90
  • 155
0

It will not work like that.

You have 2 options:

1) Iterate through all tables on code side and lunch the update for each one
2) Use a stored procedure that iterate through all tables and lunch the update for each one

Stephan
  • 8,000
  • 3
  • 36
  • 42