Обсуждение: removing duplicates - sql
Howdy:
I have a table that has a primary key on it. Somewhere in
the table, it seems to have a few duplicate records in it.
I'm trying to weed out the duplicates, but am having problems
with the sql.
To start, I create a temp table and just get the
distinct records I need.
[code]
select distinct
name,
addr1,
phone,
city
into temp_table
from emp_table ;
[/snip code]
I see by the count of the two tables there are
about 50 or so records that are in one and
not in the temp_table. How do I use the
where clause to figure out where the duplicates
are?
Suggestions? TIA!
-X
Am Donnerstag, 22. August 2002 01:52 schrieb Johnson, Shaunn:
> Howdy:
>
> I have a table that has a primary key on it. Somewhere in
> the table, it seems to have a few duplicate records in it.
> I'm trying to weed out the duplicates, but am having problems
> with the sql.
>
...
Hi,
select distinct t1.name, t1.addr1, t1.phone, t1.city
from emp_table t1
where (select count(*) from emp_table t2
where t1.name = t2.name
and t1.addr1 = t2.addr1
and t1.phone = t2.phone
and t1.city = t2.city) > 1
will do it.
Tommi
On Thursday 22 August 2002 08:13 am, Tommi Maekitalo wrote: > Am Donnerstag, 22. August 2002 01:52 schrieb Johnson, Shaunn: > > Howdy: > > > > I have a table that has a primary key on it. Somewhere in > > the table, it seems to have a few duplicate records in it. > > I'm trying to weed out the duplicates, but am having problems > > with the sql. > > ... > > Hi, > > select distinct t1.name, t1.addr1, t1.phone, t1.city > from emp_table t1 > where (select count(*) from emp_table t2 > where t1.name = t2.name > and t1.addr1 = t2.addr1 > and t1.phone = t2.phone > and t1.city = t2.city) > 1 > > will do it. or select name,addr,phone,city,count(*) from emo_table group by name,addr,phone,city having count(*) > 1 regards peco