Update field in SQL Server Table Dynamicaly
If your table is test, try this:

SELECT t1.*
    ,New_FileName = t1.FileName + CASE 
        WHEN t2.seq &gt; 1
            THEN '_' + ltrim(str(seq))
        ELSE ''
        END
FROM test t1
JOIN (
    SELECT ID
        ,Memeber_Number
        ,FileName
        ,seq = ROW_NUMBER() OVER (
            PARTITION BY FileName
            ,Memeber_Number ORDER BY ID
            )
    FROM test
    ) t2 ON t1.id = t2.id;