1

In SQL Server 2008 I have a table tblProfile with employee numbers (EmID) and profileID (ProID). EmID+ProID are a combined primary key.

I found "duplicates" on the EmID, which come from the employee table. Each employee can have multiple ProID, but the combination of both ID's is unique.

Of course, from the side of the EmID they are no duplicates, but in the employee table they are duplicates.

For example in tblEmployee there are:

EmId        Lastname      Firstname   Birthdate  
------------------------------------------------
22          Mayer          Frank      1960-01-01  
23          Meyer          Frank      1960-01-01

Now I want to "clean" the table with this logic. To clean the tblEmployee is not the problem but how to clean the tblProfile with this logic?

Let's assume I want to delete EmID = 23, because I want to keep the older record. If there is an existing profile for EmID = 23 which has not been defined for 22 it is not a problem, but if there already is a combination of 22 + 2, I would create an error when changing 23 + 2 to 22 + 2.

Data: 

EmID    ProID  
22       2  
23       2  
23       1

In that case I would have to delete 23 + 2 but change 23 + 1 to 22 + 1.

The result should be 

22       1
22       2

which is my goal.

I really do not know how to handle that.

I cannot start with 

delete from tblprofile 
where EmID = @duplicateEmID

as I delete the record which I might use to keep the profile....

Thanks your help

Michael

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mak
  • 359
  • 3
  • 14
  • it would be simpler and better just to delete the `22` record... the problem starts when you have multiple profiles for both duplicated records. – Zohar Peled Aug 27 '15 at 12:18
  • As Long as I could rely on that every employee has all his ProID I could do this, but the Problem is, that the user forgot to define 22 + 1 but when creating employee 23 (the same as 22 of course) he defined him correct. So I do not want to loose any Definition for a specific employee. I think the solution is somehow to select both and find the "Maximum" of both and then correct or delete the rest. – mak Aug 27 '15 at 12:25

1 Answers1

1

You will do this in multiple, simple steps.

1) Run a query to identify the Employee dupes that you want to delete (not the oldest). Store them in a temp table. Also add a column called "NewID" in the temp table.

2) Run a query to update the temp table and set the value of NewID to the oldest emId for that employee.

3) Run a query to insert the combination of NewID plus ProfileID into the Profile table WHERE the ProfileID currently exists with the old employee id, AND the combination of NewID & ProfileID does not already exist.

4) Delete all the rows from Profile table that have the old profileIDs in them.

Breaking down a complex issue into it's most basic steps often makes it easy to solve.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52