I have this query but I want to change the strings in every field_name found instead of manually changing this. How I can do this ?
update TABLE_NAME
set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);
-
Almost any problem that requires repeating an action for all columns in a table suggests that your schema is not properly designed. Instead of having separate columns for each of these things, they should be rows in another table that you join with, so you can easily process them with a simple query.Barmar– Barmar2012-09-25 01:52:54 +00:00Commented Sep 25, 2012 at 1:52
Add a comment
|
2 Answers
Then you have to specify all fieldnames. Example
UPDATE tableName
SET field1 = REPLACE(field1, 'oldstring', 'newstring'),
field2 = REPLACE(field2, 'oldstring', 'newstring'),
field3 = REPLACE(field3, 'oldstring', 'newstring'),
fieldN = REPLACE(fieldN, 'oldstring', 'newstring')
Comments
You may use information_schema.columns to build a query for each column
SELECT CONCAT( 'Update table ', table_name,
' set ', column_name, ' = replace(',column_name,', \‘find this string\’, \‘replace found string with this string\’); ')
FROM information_schema.columns
WHERE table_name = '<TableName>'`
This will generate update statements for all of the columns in the table (will save you time and effort to write column names manually).
