Update field in SQL Server Table Dynamicaly

Question

I need to do a 1 time update of a Sql Server table.

The table has a unique ID, contains a member number field and a FileName field which tells us what file the member data came from.

What I am needing to do is update the [Filename] value to be different for all the records pertaining to a specific member number that was present in the historical file more than once.

So in the image below Member number 123 has 3 records in the table that have FileName value as history. I need the value of Filename to be unique per member number record so I would need the Filename values for this member to be History, History_2 and History_3, instead of History for all 3 records.

I'm pretty sure this is possible to do with SQL and am currently working on it but was wondering if someone has come across a similar table update need and had some advise or code I can slightly modify to get my desired results.

This is the code I use to identify the member number that have records that need to be updated:

SELECT Member_Number, COUNT(*) CountNumber
                   FROM mytable
                   WHERE [FileName] = 'History' 
                   GROUP BY Member_Number
                   HAVING COUNT(*) > 1

Progress Update:

I got the below code so far but I am having to specify the actual member number. I guess I need to find a way to make the below code loop through all the member numbers. There are about 1,000+ member numbers that need to be updated in the table.

  SELECT *, t1.[FileName] + '_' + CAST(RN AS VARCHAR(50)) FROM 
  (
  SELECT (ROW_NUMBER() OVER (ORDER BY(SELECT 1))) RN ,* FROM mytable WHERE Member_Number = 'BAY391'
  ) AS t1
  WHERE RN > 1

Answer 1

If your table is test, try this:

SELECT t1.*
    ,New_FileName = t1.FileName + CASE 
        WHEN t2.seq > 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;

Answer 2

It depends on your dialect of SQL, many times I have seen the following approaches:

• Make a temporary table, insert the rows, and use the PK of the generated table to update the existing values.
• Use windowing function, concatenate the values in a subquery and update the main table.

For an example of windowing functions and subqueries in T-SQL(if you assume g1 is your table and not some subquery I just invented):

update g1 set g1.name = g.name
from ( 
  select id, 
  name, 
  ROW_NUMBER() OVER(ORDER BY name ) AS [num]

  from (
  select 1 [id], 'bill' [name]
  union all 
  select 2, 'bob'
  union all 
  select 3, 'bob'
  union all 
  select 4, 'bob'
) f
) g
join 
(  
  select 1 [id], 'bill' [name]
  union all 
  select 2, 'bob'
  union all 
  select 3, 'bob'
  union all 
  select 4, 'bob'
) g1 on g1.id = g.id