Hi Aruna,
Your solution for deleting the duplicate rows might fail if just the first column (COL1) is same for some rows. Hence, a safer soltuion would be,
delete from T1
where rowid not in
(select max(rowid) from T1
group by C1, C2, C3, ..., Cn);
Cheers,
Vibhor Agarwal
DBA Support
Mastek Ltd.
SEEPZ, Mumbai
Mob: +91-98195-35552
Email: [Email address protected]
----- Original Message -----
From: "Aruna Boyagane " <oracledba-ezmlmshield-x69130881.[Email address protected]
Date: Tue, 22 Jun 2004 14:23:01 -0700
To: "LazyDBA Discussion" <[Email address protected]
Subject: RE: how to delete duplicate rows?
Try this one out
delete from TAB1 where
rowid not in (select rowid from TAB1 where rowid in (select max(rowid)
from TAB1
group by COL1));
-----Original Message-----
From: amit vaidya
[mailto:oracledba-ezmlmshield-x81660791.[Email address protected]
Sent: Monday, June 21, 2004 10:20 PM
To: LazyDBA Discussion
Subject: how to delete duplicate rows?
hi gurus,
i want to delete duplicate rows per group and keep
one record per group thus reduce the redundancy. how
do i do that?
My query is -
DELETE FROM TAB1
WHERE COL1 IN ( SELECT COL1 FROM TAB1
GROUP BY COL1
HAVING COUNT(COL1)>1);
this is deleting all the duplicate rows but i want to
keep 1 row per group and delete its repetating
records.
plz provide the help
thanx in advance
Amit
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: send a blank email to oracledba-[Email address protected]
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: send a blank email to oracledba-[Email address protected]
By using this list you agree to these terms:http://www.lazydba.com/legal.html
--
India.com free e-mail - www.india.com.
Check out our value-added Premium features, such as an extra 20MB for mail storage, POP3, e-mail forwarding, and ads-free mailboxes!
Powered by Outblaze
Oracle LazyDBA home page