Add a number to one duplicate record in SQL 2

[esdad71]

Good afternoon,

I have an issue where a client updated a table combining two companies of data. Now they have about 400 records that are duplicated. Meaning, for those 400 records, there are 2 records sharing the same number for 200 pairs of employees now. I need to find an easy way to update this as I am worried it propagated over multiple tables and will need to be corrected also.

This is what I am using to 'find the duplicte'
SELECT
[p_empno],COUNT(*)

FROM [dbo].[hrpersnl]
Group by p_empno
having count(*) > 1

GO

Now that I have found them, is there a way that I can add a "1" to the second record of each of the duplicates so they will not all be duplicated through a SQL script? I have tried a few UPDATE scripts to no avail. Thank you in advance for your help!

 

[gmmastros]

Is this empno a foreign key in other tables? If so, then you have bigger problems than duplicated records. For example, if you have a joined table for daily hours, where you keep track of hours worked, vacations taken, etc... then the data would be corrupt in the other table as well.

De-duping a single table isn't too bad, but if the value in the original table is a foreign key to another table, you should probably start over, or plan on manually fixing a lot of stuff.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[esdad71]

No it is just two tables. I checked after I was told about this. The other table can be deleted and recreated/reimported but I would like to find an easy way for this table as they have edited lots of employee data since the original import last month.

Thanks for the heads up!

 

[gk53]

What you can do is. Select all duplicate records into temp table and delete all duplicates from original. After that insert into original records from temp table (only records needs to be inserted)

 

[gmmastros]

Make sure you back up your database before doing this.... just in case:

; With Dupes As
(
  Select p_empno
  From   [dbo].[hrpersnl]
  Group By p_empno
  Having Count(*) > 1
)
, SecondDupe As
(
  Select T.*,
         Row_Number() Over (Partition By T.p_empno order by T.p_empno) As Rowid
  from    [dbo].[hrpersnl] T
         Inner Join Dupes
  	     On T.p_empno = Dupes.p_empno
)
Update SecondDupe
Set    p_empno = p_empno + '1'
Where  RowId = 2

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[esdad71]

gmmastros - you are the man! That is exactly what I was needing. I had something close but it would not finish.

Have a good one!