1

I have a database with name test having two tables

  1. sample1(name, quote)
  2. sample2(name, quote)

I need a replace function or procedure that does the following

  1. Find columns with String data type (Text,varchar,char,etc) in all tables within test database
  2. Look for a value in that column and replace it with a new value

I have found an stored procedure in this post but I don't understand how it works.

is there anyway to achieve this or adopt the stored procedure in the linked post for my use?

  • can you elaborate your questions little bit more in detail. as I can see you have two questions you want to replace something in db and how it is being related to table_schema??? – MySQL DBA Jul 29 '15 at 6:11
  • with respect to replacing a specific text in entire databases. You can refer to solution provided here :stackoverflow.com/questions/4822638/… – MySQL DBA Jul 29 '15 at 6:13
  • Regarding table_scheme I don't know what is this and how I can get it from phpmyadmin. can you write a sample query for it. – Developer Jul 29 '15 at 6:18
  • table_schema is nothing but your database name. If you query informaiton_schema.tables you get the details. – MySQL DBA Jul 29 '15 at 6:38
  • You searched a lot and found `table_schema'? where did you find it? How is it related to the remaining text and the title of your post? – miracle173 Jul 29 '15 at 7:19
1

MySQL's replace function should come handy to replace the text you need:

update $table set column = replace(column, 'old_text', new_text') where column='old_text' and <other_conditions>;

Now, as you talk of replace in all tables, that's something not possible in single query. Though you can create a routine to do so! For the sample you can refer a procedure which is finding a string in all tables of all databases.

  • (Though been already mentioned in comments) table_schema is a column name of information_schema.tables table which represents the database name.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.