Link to home
Start Free TrialLog in
Avatar of Member_2_7967487
Member_2_7967487

asked on

Change variables in SQL table

Hi,

How do I change all highlighted to D: drive in SQL with one query.

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You don't need to execute the command several times:
UPDATE dbo.Company
SET 
   ACCTdirect = REPLACE (ACCTdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'), 
   EDIdirect = REPLACE (EDIdirect , 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'), 
   vpEDIdirect = REPLACE (vpEDIdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'), 
   vpSharedirect = REPLACE (ACCTdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:')

Open in new window

Of course, if your columns are case insensitive already then the COLLATE is not necessary.
Try below

UPDATE dbo.Company
SET
   ACCTdirect = REPLACE (ACCTdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'),
   EDIdirect = REPLACE (EDIdirect , 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'),
   vpEDIdirect = REPLACE (vpEDIdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:'),
   vpSharedirect = REPLACE (ACCTdirect, 'C:\DataMasons' COLLATE Latin1_General_CI_AS , 'D:')
Avatar of Member_2_7967487
Member_2_7967487

ASKER

Jim's feedback works best. Thank you, Jim!!

Thanks you everyone for your help!
Thanks for the accept.  Good luck with your project.  -Jim

@pcelba and pawan - I'm glad you guys posted, as I didn't know you can combine REPLACE with COLLATE.  I can predict sitting for a SQL 2016 T-SQL exam and getting that as a question, and not answering it correctly if it were not for reading your posts.
BTW, I wouldn't say Jim's solution works best it was just faster but it also produces double backslashes on output...  :-)

np for me