SQL Update various rows using the replace technique

Question

I need to resolve an issue in SQL in a table that I have.

I have a table which has various columns (and also various rows) which have a route. I.E.

E:\file.xml

What I need to do is, check at least 10 columns that are much alike, and search for any word that starts with E:\ and replace it with C:.

Is there a query that could help me do that? or would I have to edit those one by one (because I don't think that's the smartest way).

UPDATE: TABLE STRUCTURE

CREATE TABLE [dbo].[CCARD_corporatives](
    [idCorporative] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [pathCFD] [varchar](128) NULL,
    [pathCertificateCFD] [varchar](128) NULL,
    [pathPrivateKeyCFD] [varchar](128) NULL,
 CONSTRAINT [PK_CCARD_corporatives] PRIMARY KEY CLUSTERED 
(
    [idCorporative] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


SET IDENTITY_INSERT [dbo].[CCARD_corporatives] ON
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (19, 'E:\file1.xml', 'E:\fil2e.xml', 'E:\file423.xml')
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (33, 'E:\file1.xml', 'E:\filer2.xml', 'E:\file123.xml')
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (34, 'E:\file1.xml', 'E:\filedsf.xml', 'E:\fil33e.xml')
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (36, 'E:\file1.xml', 'E:\f4sdfile.xml', 'E:\fil44e.xml')
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (37, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (38, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (40, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (41, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (42, 'E:\file1.xml', 'E:\filgbvdfe.xml', 'E:\fileg dfegdfs.xml')
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (43, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (44, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (49, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (50, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (51, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (52, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (53, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (56, 'E:\file1.xml', 'E:\file0067.xml', 'E:\file4323.xml')
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (57, 'E:\file1.xml', 'E:\file4234.xml', 'E:\fil555e.xml')
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (64, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (82, NULL, NULL, NULL)
INSERT [dbo].[CCARD_corporatives] ([idCorporative], [pathCFD], [pathCertificateCFD], [pathPrivateKeyCFD], ) VALUES (83, 'E:\file1.xml', 'E:\file443.xml', 'E:\fil4324e.xml')
SET IDENTITY_INSERT [dbo].[CCARD_corporatives] OFF

what is the structure of your table? could be te route value in any of the columns or do you have a specific column for that value? — Silvano, Aug 30 '13 at 17:57
When you say "*to edit those one by one*" do you mean one **column** or one **field**? — PM 77-1, Aug 30 '13 at 18:04
PM 77-1 I mean, when you click on Edit Top 200 Rows, you get to edit any cell as you please manually... so I wouldn't go by editing like 200 cells one by one... — Victor, Aug 30 '13 at 18:07
**This link will also help you.** [Mysql Update multiple rows](https://stackoverflow.com/questions/17894048/mysql-update-mutliple-row-of-datas/50113207#50113207) — Shubham Verma, May 01 '18 at 08:07

score 0 · Accepted Answer · answered Aug 30 '13 at 18:17

If you want to update every column that actually has any value wich starts with e:\, try this:

UPDATE [CCARD_corporatives]
SET pathCFD = CASE 
            WHEN pathCFD LIKE 'e:\%' 
            THEN REPLACE(pathCFD,'e:\','c:\') 
            ELSE pathCFD 
           END,
         pathCertificateCFD = CASE 
                        WHEN pathCertificateCFD LIKE 'e:\%' 
                        THEN REPLACE(pathCertificateCFD,'e:\','c:\') 
                        ELSE pathCertificateCFD 
                    END,
        pathPrivateKeyCFD = CASE 
                        WHEN pathPrivateKeyCFD LIKE 'e:\%' 
                        THEN REPLACE(pathPrivateKeyCFD,'e:\','c:\') 
                        ELSE pathPrivateKeyCFD 
                    END;

SQL Update various rows using the replace technique

1 Answers1