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
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