Menu
How do I update different columns and rows across a table? I want to do something similiar to replace a string in SQL server
I want to do this but the value exists in multiple columns of the same type. The values are foreign keys varchars to an employee table. Each column represents a task, so the same employee may be assigned to several tasks in a record and those tasks will vary between records. How can I do this effectively? Basically something of a replace all accross varying columns throughout a table.
Thanks for any help or advice.
Cheers, ~ck in San Diego
972
To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.
We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.
SELECT REPLACE(REPLACE(REPLACE(REPLACE('3*[4+5]/{6-8}', '[', '('), ']', ')'), '{', '('), '}', ')'); We can see that the REPLACE function is nested and it is called multiple times to replace the corresponding string as per the defined positional values within the SQL REPLACE function.
If you wanted to replace the words with blank string, go with REGEXP_REPLACE() . If you want to replace the words with other words, for example replacing & with and then use replace() . If there are multiple words to be replaced, use multiple nested replace() .
This should do the trick:
UPDATE table1
SET field1 = replace(field1, 'oldstring', 'newstring'),
field2 = replace(field2, 'oldstring2', 'newstring2')
etc...
185
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
