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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.