update help

I have a table:
id int PK
SSN varchar(15)
FirstName varchar(30)
LastName varchar(30)

Since the id is an auto increment number, i have so-called 'duplicate' records in the table..  However, the rows are not the same.  For example i could have:
1   111-22-3333  Jason  Turner
2   111-22-3333 <no name>

I want to update all the records where the ssn are the same and the other columns do not match..

thanks in advance
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
let's give this a try:

  SET FirstName = coalesce( a.firstname, b.Firstname )
  , LastName = coalesce ( a.lastname, b.LastName )
FROM tblA a
JOIN tblA b
  on a.SSN = b.SSN
 and a.ID <> b.ID
 and (a.Firstname is null or a.lastName is null)
 and b.firstname is not null
 and b.lastname is not null

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I want to update all the records where the ssn are the same
which of the records do you want to update, what is the rule?
which of the columns do you want to update, taking what input?
I assume you want to make the records the same, except for the ID. That begs the question, which values do you want to keep?  Also, my advice would be to get to the root of the problem and eliminate the duplicates and the need to keep them.

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

catwalkAuthor Commented:
I want only want to keep ssn and only update columns that are null.

so in the example above:
1   111-22-3333  Jason  Turner
2   111-22-3333 <no name>

I would update firstname and lastname.  Can't update ID (as this is PK) and do not want to update ssn.

i have found cause of issue and it has been resolved - but now must clean this up
Guy Hengel [angelIII / a3]Billing EngineerCommented:
we understood that, but from which row to which row do you want to update?
And what to do if firstname is null, but lastname is not.
catwalkAuthor Commented:
i want to update the row with less data.   If both are null - then i will leave the column as null.

There will never be the case where there is conflicting information.  Either one row has it or it doesn't.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.