0

I've been using the following query to replace certain data:

UPDATE wx3_t1 SET umo = REPLACE(umo, 'stringbefore', 'string after');

  • wx3_t1 = Table
  • umo = Column

I am looking for a way to update across the entire database, without needing to put the table or the column into the query.

Something as simple as just REPLACE('stringbefore', 'string after')

I realize that doing it this way is really aggressive but that's fine.

Ryflex
  • 5,559
  • 25
  • 79
  • 148
  • I don't think there is one native query that can do this - but you can do it manually using phpMyAdmin – Pekka Mar 02 '16 at 21:38
  • I dont understand what is the question here. You already have a query working and want a solution doesnt exist but looks better? – Juan Carlos Oropeza Mar 02 '16 at 21:43
  • @JuanCarlosOropeza I have 300 tables with many many columns, I want an aggressive replace rather than having to change every single table and column manually. – Ryflex Mar 02 '16 at 21:46
  • I just answer a question very similar. But in that case only `LOOP` for columns, you will need loop for tables as well. http://stackoverflow.com/questions/35758493/searching-through-all-columns-in-a-table/35758643?noredirect=1#comment59190809_35758643 – Juan Carlos Oropeza Mar 02 '16 at 21:53
  • Have you considered using a dynamic query (using SELECT from information_schema to get all the column names, and then using exec sp_executesql with parameters to get it to run in every table)? – phroureo Mar 02 '16 at 22:36

1 Answers1

0

You can create a simple script that query information_schema.COLUMNS to get list of all your columns by table:

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from information_schema.COLUMNS;

Then you have to iterate on the result to play your UPDATE query.

metfan
  • 98
  • 2