brgdotnet
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Only 10%? What are the 90% that are missing?
ASKER