0

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
4

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.