Re: How to delete duplicate rows with sql
In article <D84EDq.6ss@nntpa.cb.att.com>, siva@hogpe.ho.att.com (-S.POTHIREDDY) says:
>
>To all sql experts:
>
>Could any body tell me how to delete duplicate rows in a table. I was
>able to list the duplicate rows in table like this:
>
>select rowid, a.*
> from a, b {a & b are aliases for sam table. basically a self join}
> where a.rowid != b.rowid;>
>I can't this select statement as subquery because we can not the update
>the table in a subquery. Any ideas ... please. BTW we have not yet
>installed 4gl yet.
>
>Thanks in advance.
>
>Siva Pothireddy
>
>AT&T
I recommend you to create a temporary table with unique rows like this:
select unique a.*
from a
into temp uniq_a;
drop table a; |You can replace this two instructiona by
create table a (....); | a one delete but it is a time consuming
| process.
then
insert into a (select * from uniq_a); If anyone kwons a better way, please tell me how!!