Update Path

Question

Update Path

hoseam

SSCertifiable

Points: 5533
More actions
August 11, 2014 at 1:29 am

#288907

Hi, I have table below with data:
CREATE TABLE [dbo].[TblUpdate](
[uid] [int] IDENTITY(1,1) NOT NULL,
[AccessGroup] [varchar](50) NULL,
[ReportPath] [varchar](250) NULL)
INSERT INTO [TblUpdate]
VALUES('Banks', '/AWDBI/WORK FINALISED/AGE ANALYSIS'),
('Banks', '/AWDBI/WORK FINALISED/AGE ANALYSIS/DETAILS'),
('Banks', '/AWDBI/WORK IN PROGRESS/AGE ANALYSIS/DETAILS'),
('COE', '/AWDBI/WORK FINALISED/AGE ANALYSIS'),
('COE', '/AWDBI/WORK FINALISED/AGE ANALYSIS/DETAILS'),
('COE', '/AWDBI/WORK IN PROGRESS/AGE ANALYSIS/DETAILS'),
('CONTRACTS', '/AWDBI/WORK FINALISED/AGE ANALYSIS/DETAILS'),
('CONTRACTS', '/AWDBI/WORK IN PROGRESS/AGE ANALYSIS/DETAILS'),
('CONTRACTS', '/AWDBI/WORK RECEIVED/AGE ANALYSIS/DETAILS')
I want to update Column [ReportPath] so that the path in that column start with '/Development', then keep the rest of the path. I want to write that in one single SQL query, is it possible?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

hoseam (8/11/2014)
Hi, I have table below with data:
CREATE TABLE [dbo].[TblUpdate](
[uid] [int] IDENTITY(1,1) NOT NULL,
[AccessGroup] [varchar](50) NULL,
[ReportPath] [varchar](250) NULL)
INSERT INTO [TblUpdate]
VALUES('Banks', '/AWDBI/WORK FINALISED/AGE ANALYSIS'),
('Banks', '/AWDBI/WORK FINALISED/AGE ANALYSIS/DETAILS'),
('Banks', '/AWDBI/WORK IN PROGRESS/AGE ANALYSIS/DETAILS'),
('COE', '/AWDBI/WORK FINALISED/AGE ANALYSIS'),
('COE', '/AWDBI/WORK FINALISED/AGE ANALYSIS/DETAILS'),
('COE', '/AWDBI/WORK IN PROGRESS/AGE ANALYSIS/DETAILS'),
('CONTRACTS', '/AWDBI/WORK FINALISED/AGE ANALYSIS/DETAILS'),
('CONTRACTS', '/AWDBI/WORK IN PROGRESS/AGE ANALYSIS/DETAILS'),
('CONTRACTS', '/AWDBI/WORK RECEIVED/AGE ANALYSIS/DETAILS')
I want to update Column [ReportPath] so that the path in that column start with '/Development', then keep the rest of the path. I want to write that in one single SQL query, is it possible?

So are you saying that you want the path to change from '/AWDBI/WORK FINALISED/AGE ANALYSIS' to '/Development/AWDBI/WORK FINALISED/AGE ANALYSIS' ? Or from '/AWDBI/WORK FINALISED/AGE ANALYSIS' to '/Development/WORK FINALISED/AGE ANALYSIS' ?

If the first one, can't you just do this: -

UPDATE [dbo].[TblUpdate]

SET [ReportPath] = '/Development' + [ReportPath];

Which returns: -

uid AccessGroup ReportPath

----------- -------------------------------------------------- ------------------------------------------------------------

1 Banks /Development/AWDBI/WORK FINALISED/AGE ANALYSIS

2 Banks /Development/AWDBI/WORK FINALISED/AGE ANALYSIS/DETAILS

3 Banks /Development/AWDBI/WORK IN PROGRESS/AGE ANALYSIS/DETAILS

4 COE /Development/AWDBI/WORK FINALISED/AGE ANALYSIS

5 COE /Development/AWDBI/WORK FINALISED/AGE ANALYSIS/DETAILS

6 COE /Development/AWDBI/WORK IN PROGRESS/AGE ANALYSIS/DETAILS

7 CONTRACTS /Development/AWDBI/WORK FINALISED/AGE ANALYSIS/DETAILS

8 CONTRACTS /Development/AWDBI/WORK IN PROGRESS/AGE ANALYSIS/DETAILS

9 CONTRACTS /Development/AWDBI/WORK RECEIVED/AGE ANALYSIS/DETAILS

If the second one, then you'd need to use a replace, which will depend on whether or not the root of the path is always AWDBI.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/