-3

How to update all the value using MySQL query? for example i want to change all the word 'dog' to 'cat' which is located in different table rows. I usually use the line code below to update a single table row

UPDATE my_table SET my_row = REPLACE (my_row, 'dog','cat');
JeVic
  • 681
  • 1
  • 11
  • 33
  • You want to change all occurences in every field of the table? How many columns are in the table? – steinmas Mar 06 '14 at 18:47
  • In this context, by _row_ do you actually mean _column_? You want to replace 'dog' with 'cat' in all table columns? – Michael Berkowski Mar 06 '14 at 18:47
  • possible duplicate of [Replace all fields in MySQL](http://stackoverflow.com/questions/3145076/replace-all-fields-in-mysql) – Michael Berkowski Mar 06 '14 at 18:48
  • 2
    The best way I understand, your "code line" already solves your problem. What are we missing? (Of course, it would always operate on the whole table, never just one row without a `WHERE` clause) –  Mar 06 '14 at 18:50
  • 1
    my_row is a confusing name for a colum – Strawberry Mar 06 '14 at 18:54
  • @Strawberry That depends. Is it the only column? (besides, isn't it so much easier than trying to name a row my_row?) –  Mar 06 '14 at 18:55
  • Can't argue with that. – Strawberry Mar 06 '14 at 18:58
  • for example, in a wordpress database, you want to replace all the word 'dog' to 'cat' which is found in sidebar, page, post, widget, custom field, username, so instead of doing it one by one, It would be nice to do it in one query command, the answer that Michael link to be for one table row, what I am looking for is all table, all row – JeVic Mar 07 '14 at 19:35

1 Answers1

0

You will need to add an item to the SET portion of your query, for every column in the table.

UPDATE my_table 
SET my_row = REPLACE (my_row, 'dog','cat'),
my_row_2 = REPLACE(my_row_2,'dog','cat'),
.
.
.
my_row_n = REPLACE(my_row_n,'dog','cat')

I'm sure you meant to use my_column instead of my_row, but I kept my_row here for consistency.

steinmas
  • 398
  • 3
  • 9
  • I'm just not convinced the OP was looking for something multi-column. Definitely something missing from the question though. Maybe there's available code and someone not understanding how it woks? –  Mar 06 '14 at 18:53