Another option is to use ANY clause

DELETE FROM EMP E
WHERE E.ROWID > ANY (SELECT ROWID
FROM EMP M
WHERE M.EMPNO = E.EMPNO
AND M.ENAME = E.ENAME
AND M.JOB = E.JOB )


Hope that helps
All the best

Ajay


-----Original Message-----
From: Shruti K. Mittal
[mailto:oracledba-ezmlmshield-x51938138.[Email address protected]
Sent: Wednesday, August 18, 2004 10:32 AM
To: LazyDBA Discussion
Subject: RE: how to find duplicate copy...

Hi,

select col1, col2, col3, rowid from table1 a
where rowid > (select min (rowid) from table1 b
where b.col1 = a.col1
and b.col2 = a.col2
and b.col3 = a.col3)
Order by col1, col2, col3;

will give you just the duplicate ones i.e. leave out 1st record and
return
the other ones found to be duplicate, so can be used to delete the
duplicate
ones using rowid.

regards,
./shruti



Oracle LazyDBA home page