Problem solve Get help with specific problems with your technologies, process and projects.

Removing duplicate rows

I have a table with primary key on empid and deptid. Accidentally the key got dropped and some duplicate records were inserted into the table. Can I delete the duplicate records using a sinqle query so that I can create the primary key again?

Yes, you can, but maybe not in a single query. The exact SQL required depends largely on which of the duplicate rows you want to keep, and partly on which database system you're using (because of minor differences in SQL syntax).

My condolences to you. "Accidentally got dropped" is not as funny as it sounds, right?

This "duplicates" question comes up all the time, and the answer is never simple. "Duplicates" is in quotes because there are typically never any true duplicate rows, just duplicate keys which aren't actual keys, such as when a PRIMARY KEY or UNIQUE constraint is accidentally dropped, or never declared in the first place.

Without knowing what other columns are in the table along with empid and deptid, it's difficult to suggest a strategy for which of the "duplicate" rows you want to keep. For example, suppose your table looks like this:

empid deptid salary
 21    101    32000
 21    101    28000
 21    123    35000
 37    555    27600
 45    200    43000
 45    200    47000

In the above example, how do you decide which row to keep? Would you just average the salary for each employee? Take the lowest salary? The highest?

On the other hand, if there really is no other column in the table along with empid and deptid, then there are true duplicate rows, so the following SELECT DISTINCT strategy will work:

select distinct empid, deptid
  into newtable
  from maintable

delete from maintable
insert into maintable select empid, deptid from newtable
alter table maintable add constraint mypkey primary key (empid, deptid)

As always, when running any SQL that changes data, it's a good idea to take a backup first.

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.