Update field in table for all records using a select statement

A previous developer created a table that stores the absolute path to files in our server. I want to convert them to relative paths instead.

I already wrote the portion that properly strips the string down to a relative path. My issue is understanding how to basically update each record, with a new version of its own string.

Here is what I originally tried:

UPDATE LFRX_Attachments
    SET [File] = (SELECT TOP 1 SUBSTRING([File], PATINDEX('%Files\%', [File]) + 6, LEN([File]))
                     FROM LFRX_Attachments A
                     WHERE [Type] = 4 AND AttachmentId = A.AttachmentId)

However, this tanked in epic fashion by just overwriting every recor开发者_Go百科d to have the value of the first record in the table. Any suggestions?

UPDATE LFRX_Attachments
SET [File] = SUBSTRING([File], PATINDEX('Files\', [File]) + 6, LEN([File]))
WHERE [Type] = 4 

From a readability/maintenance standpoint, you're better off selecting for the data you want to alter, then iterating through the result set and updating each record separately.

Does this work for you?

UPDATE LFRX_Attachments SET [File] = SUBSTRING([File], PATINDEX('Files\', [File]) + 6, LEN([File]))