How to loop through table and update a column value in SQL-CodePudding

I have the table tFile in my database, I want to loop through the table and update filename as shown in this example:

 id    fileId     filename
 -------------------------
 231    555        Null
 123    444        Null
 572    732        Null

I want to update the filename to be the name(fileId) as shown here:

 id    fileId      filename
 ----------------------------
 231    555        test(555)
 123    444        test(444)
 572    732        test(732)

I wrote a SQL script that update just one filename according to writing it manually but I need to update all filename using loop. I think I have to do nested loop one to loop through fileId and other to loop through id.

But I'm sorry I have no experience about this. I need help please.

update tFile
set filename = 'test'  '(' fileId ')';
where id in (231)

CodePudding user response：

As per your expected output, I've created a sample table please have a look.

DECLARE @tFile TABLE
(
    Id int IDENTITY(1,1),
    fileId    INT,
    filename NVARCHAR(50)
)

INSERT INTO @tFile VALUES(555,NULL),(444,NULL),(732,NULL)
SELECT *,CONCAT('TEST(',fileId,')') AS [FileNameUPdate] FROM @tFile
UPDATE @tFile
SET filename  =CONCAT('TEST(',fileId,')')

SELECT * FROM @tFile

Output