We help IT Professionals succeed at work.
Get Started

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

brgdotnet
brgdotnet asked
on
89 Views
Last Modified: 2015-02-27
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
Comment
Watch Question
Associate Principal Engineer
CERTIFIED EXPERT
Top Expert 2014
Commented:
This problem has been solved!
Unlock 2 Answers and 4 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE