Remove Duplicate Rows without a Primary Key

[Damien Cobbs]

Is there a way to remove duplicate rows from a table that does not
have a primary key leaving one copy of the row without comparing each
column to each other as suggested by the code example below:

delete from foo x
where x.rowid <(
select min(rowid) from foo y
where y.primary key = x.primary key
)

Any ideas?

[nobody]

Re: Remove Duplicate Rows without a Primary Key

delete from foo x
where x.rowid <(
select min(rowid) from foo y
where y.field1||y.fie ld2 etc = x.field1||x.fie ld2 etc

"Damien Cobbs" <damien.cobbs@g d-ais.comwrote in message
news:4c413633.0 310270818.a8b0f 9e@posting.goog le.com...

Is there a way to remove duplicate rows from a table that does not
have a primary key leaving one copy of the row without comparing each
column to each other as suggested by the code example below:
>
delete from foo x
where x.rowid <(
select min(rowid) from foo y
where y.primary key = x.primary key
)
>
Any ideas?

[LKBrwn_DBA]

Re: Remove Duplicate Rows without a Primary Key


You can do this:



RENAME FOO TO FOO_BKP;



CREATE TABLE FOO AS

SELECT DISTINCT * FROM FOO_BKP;


--
Posted via http://dbforums.com