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]

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

"Damien Cobbs" <da**********@gd-ais.com> wrote in message
news:4c*************************@posting.google.co m...

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]

You can do this:

RENAME FOO TO FOO_BKP;

CREATE TABLE FOO AS

SELECT DISTINCT * FROM FOO_BKP;
--
Posted via http://dbforums.com