EXPERT RESPONSE
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.
|
