SQL - Update Duplicate Record

I have an anomaly in my database and I need to fix it.  Trying to figure out what caused it but in the meantime I need to fix the issue.  Have two tables.  Person and Employee.

Person has below fields (I'll only use the fields that are important).
Person.Id, Person.FN, Person.LN, Person.SN

Employee has the below fields (I'll only use the fields that are important)
Employee.EmpId, Person.Id, Employee.CID, Employee.FID

The issue is that there are two person records and two employee records.  
The two person records are John Doe and have two separate Employee records.  So lets say

Person
123, John, Doe, 123456789
234, John, Doe, 123456789

Employee
567, 123, 888, 999
789, 234, NULL, NULL

The issue I have is the nulls. I have approximately 500 records that have this issue.  

How can I write an update statement to easily update record Employee.CID, Employee.FID of 789 with the Employee.CID, Employee.FID of 567 for the same person?

Update Employee
Set Employee.CID = (.....),
Employee.FID = (.....)

Thanks
LVL 2
CipherISAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Please post concise and complete examples. Include Table DDL and sample data insert statements.

E.g.

DECLARE @Person TABLE ( ID INT, SN INT );

INSERT  INTO @Person
VALUES  ( 123, 123456789 ),
        ( 234, 123456789 );

DECLARE @Employee TABLE
    (
      EmpId INT ,
      PersonId INT ,
      CID INT ,
      FID INT
    )

INSERT  INTO @Employee
VALUES  ( 567, 123, 888, 999 ),
        ( 789, 234, NULL, NULL ),
        ( 789, 235, NULL, NULL );

UPDATE  E1
SET     CID = COALESCE(E1.CID, E2.CID) ,
        FID = COALESCE(E1.FID, E2.FID)
FROM    @Employee E1
        INNER JOIN @Person P1 ON E1.PersonId = P1.ID
        INNER JOIN @Person P2 ON P2.SN = P1.SN
                                 AND P2.ID != P1.ID
        INNER JOIN @Employee E2 ON E2.PersonId = P2.ID
WHERE   ( E1.CID IS NULL
          OR E1.FID IS NULL
        )
        AND E1.EmpId != E2.EmpId;

SELECT  *
FROM    @Employee E;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
How do you know which values should replace the NULL values?
CipherISAuthor Commented:
Thank You
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.