Sql-server – Update field in SQL Server Table Dynamicaly

sql serverupdate

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.

Table Update Example

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

Best Answer

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;
Related Question