Home » SQL & PL/SQL » SQL & PL/SQL » how to delete duplicate rows
how to delete duplicate rows [message #189518] Fri, 25 August 2006 00:14 Go to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

dear friends,
suppose i've one table, it has only to rows.the two rows are are same as it is.how can i delete one row from that table?


thank you verymuch
Re: how to delete duplicate rows [message #189533 is a reply to message #189518] Fri, 25 August 2006 01:00 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Method 1: 

   SQL> DELETE FROM table_name A WHERE ROWID > (
     2    SELECT min(rowid) FROM table_name B
     3    WHERE A.key_values = B.key_values);
Method 2: 

   SQL> create table table_name2 as select distinct * from table_name1;
   SQL> drop table_name1;
   SQL> rename table_name2 to table_name1;
   SQL> -- Remember to recreate all indexes, constraints, triggers, etc on table... 

Method 3: 
   SQL> delete from my_table t1
   SQL> where  exists (select 'x' from my_table t2
   SQL>                 where t2.key_value1 = t1.key_value1
   SQL>                   and t2.key_value2 = t1.key_value2
   SQL>                   and t2.rowid      > t1.rowid);

Method 4: 
SQL> delete from my_table where rowid in (select rowid from (select rowid, row_number() over(partition by key_value1, key_value2 order by key_value1, key_value2) seq from my_table t) where seq <>1);

Method 5:
SQL> delete from my_table 
 where rowid not in (select min(rowid)
                       from my_table group by key_value1,key_value2);



Thanks,
Thangam
Re: how to delete duplicate rows [message #189538 is a reply to message #189533] Fri, 25 August 2006 01:13 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

it is very great to receive answere for my question sir.nice queries. thankyou very much
Re: how to delete duplicate rows [message #190031 is a reply to message #189518] Tue, 29 August 2006 01:52 Go to previous messageGo to next message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
Hi,
You can delete two rows having same values like this:

"delete from tablename where rowid in(select rowid from tablename minus (select max(rowid) from tablename group by(columnname)))"

Hope this may solve your problem.

Regards
Jaydeep Mitra
/
Re: how to delete duplicate rows [message #190190 is a reply to message #189518] Tue, 29 August 2006 11:10 Go to previous messageGo to next message
vikasg2006
Messages: 78
Registered: March 2006
Location: Dubai
Member
hy
method 1 is best and fast given by mr Thangam.

vikas
Re: how to delete duplicate rows [message #190196 is a reply to message #190190] Tue, 29 August 2006 12:00 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
But, actually Method 4 will improve the performance than others.
check the link given bleow for details.

http://www.akadia.com/services/ora_analytic_functions2.html


Thanks,
Thangam

Previous Topic: How to Change One word in a particular column
Next Topic: Passing a recordset into a stored procedure from java
Goto Forum:
  


Current Time: Sun Dec 11 00:38:14 CST 2016

Total time taken to generate the page: 0.11474 seconds