Link to home
Start Free TrialLog in
Avatar of brgdotnet
brgdotnetFlag for United States of America

asked on

Need to change only the first row in a table that has duplicate records

Suppose I have a database table with members of a club. Each member is assigned a unique id. In the club table I have a column that is used to mark a row if it is a duplicate of other rows. This column is named "IsDup". so if you look at the table below all duplicate rows are marked with a  column value of "1" in the IsDup column.
Below is the table, and I have attached a sample table having scripted it out with insert statements in sql server.

I need to update the data in the table, so that the very first duplicate is marked with a value of "8" in the IsDup column, and the other duplicates for a club member retain their IsDup value of "1". (Note if a club member does not have duplicates, then his or her IsDup column is not modified). Can someone help me create the update statement for this?

Below is an example of my ClubMembers table before and after the update.

Before update
 ID      Fname  Lname     IsDup
95X    Narnia   Jones,      1
95X    Narnia   Jones,      1
95X    Narnia   Jones,      1
89X    Tamra   Gleeson   NULL
90E     Amy     Sloan        1
90E     Amy     Sloan

After (Update)

 ID      Fname  Lname     IsDup
95X    Narnia   Jones,      8
95X    Narnia   Jones,      1
95X    Narnia   Jones,      1
89X    Tamra   Gleeson   NULL
90E     Amy     Sloan        8
90E     Amy     Sloan        1
TheClub.txt
ASKER CERTIFIED SOLUTION
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of brgdotnet

ASKER

Thanks Guys. Vitors solution worked 10%. Vikas, yours was close, but the people with only one record were having their IsDup flag set to 8, and I needed that value to not change. Plus I like Vitors solution because it did not involve a temp table. Thank you guys so much.
Only 10%? What are the 90% that are missing?