UPDATE your_table SET name_col = REPLACE(name_col, 'Peter', 'Paul');
Now, when you say, "all fields in a table", do you mean every row of a particular column, or every row of all columns? If the latter, I expect you'll have to update them all separately,
UPDATE your_table
SET col1 = REPLACE(col1, 'Peter', 'Paul'),
col2 = REPLACE(col2, 'Peter', 'Paul'),
col3 = REPLACE(col3, 'Peter', 'Paul'),
col4 = REPLACE(col4, 'Peter', 'Paul'),
... last_col = REPLACE(last_col, 'Peter', 'Paul');or do as Kevin suggested: dump the table, search and replace, and reload (though I'd use sed instead of vi).
Michael
Kevin Spencer wrote:
On Fri, 12 Nov 2004 22:12:29 -0500, Joshua Beall <[EMAIL PROTECTED]> wrote:
Hi All,
I would like to search through all fields in a table, and anytime a search string comes up, have it replace it with another string. By way of example, let's say I wanted to replace every occurence of 'Peter' with 'Paul' - can I do this purely with SQL?
I know I could do it in PHP fairly easily, but I am wondering if there is a way I can just feed a query to MySQL that will take care of things.
How big is the table? If it's not that big, dump it to a file via mysqldump, open the file in vi, do a global search and replace, re-load the table, and you're done.
-- Kev.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
