[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

update help

I have a table:
tblA:
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
0
catwalk
Asked:
catwalk
  • 3
  • 2
  • 2
1 Solution
 
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?
0
 
dqmqCommented:
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.

0
 
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
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
we understood that, but from which row to which row do you want to update?
0
 
dqmqCommented:
And what to do if firstname is null, but lastname is not.
0
 
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's give this a try:

UPDATE a
  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

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now