Re: how to compare two line of the same table and delete one

"Gregory loth" <loth_at_mdeo.fr> wrote in message news:NqAk5.14$qA2.1053_at_psinet-eu-nl...
>sql: how to compare two line of the same table and delete one thanks

Like, delete one when two are the same?

One way (not sure if this is the only way but it'll work, however it could be slow because of a subquery) is this. Basically, select all rows that occur more than once and delete one of them, arbitrarily using ROWID:

Assume for this example that I have a code-description table (my_table) like this:

CODE VARCHAR2(3)
DES VARCHAR2(40) ...and I want CODE to be unique.

delete my table where rowid in(
select max(rowid)
from mytable a
having count(code) > 1
group by code);

It's gonna be s-l-o-w though.

-Matt Received on Thu Aug 10 2000 - 00:00:00 CEST