Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pvccaz
Yak Posting Veteran
87 Posts |
 Posted - 2012-07-18 : 20:11:23
|
| Hi,I have a table with 3 columnsSample data in the table1:Id Name Value1 Location11 \\server1\\Folder19\Subfolder2 Location22 \\server1\\Folder15\Subfolder3 Location33 \\server1\\Folder13\Subfolder4 Location44 \\server1\\Folder12\Subfolder5 Location55 \\server1\\Folder17\Subfolder6 Location66 \\server1\\Folder14\SubfolderSince the server1 location is changed i would like to update this server1 to server23 in all the rows that has values like server1.(select * from table1 where value like '%\\server1%')The individual update statements would be like this:update table1 set value = '\\server23\\Folder19\Subfolder'where value = '\\server1\\Folder19\Subfolder'The Name and Id columns are different the only common parameter is \\sever1\\ in the rows.Instead of writing individual update statements, how can i write oneupdate statement that will update server1 to server23 in all the rows that has server1 in it?. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-18 : 20:14:11
|
| [code]update table1 set value = REPLACE(value,'\\server1\'.'\\server23\')WHERE value LIKE '\\server1\%'[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
pvccaz
Yak Posting Veteran
87 Posts |
Posted - 2012-07-19 : 12:07:07
|
| Perfect !!.. That worked.! Thank you. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-19 : 13:08:34
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Update query