CODEWITHSUNDEEP
sql
Matthew Cox
Matthew Cox

Reputation: 13672

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 record to have the value of the first record in the table. Any suggestions?

Upvotes: 2

Views: 291

Answers (3)

MLF
MLF

Reputation: 619

Does this work for you?

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

Upvotes: 0

Lamak
Lamak

Reputation: 70638

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

Upvotes: 4

Brian Bauman
Brian Bauman

Reputation: 668

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.

Upvotes: 1

Related Questions