?
Solved

select the row with duplicated value from oracle table

Posted on 2011-04-28
3
Medium Priority
?
395 Views
Last Modified: 2012-05-11
oracle table with column name, but there are some duplicated name value inside the table, how to select those rows ? how to delete these rows, for example if 2 rows with name=Tom, I need delete 1 of 2.

thanks
0
Comment
Question by:keepworking
3 Comments
 
LVL 20

Accepted Solution

by:
gatorvip earned 2000 total points
ID: 35487204
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4301254394074

1. Select

select * from t
  where rowid in ( select rid
                     from ( select rowid rid,
                                   row_number() over
                                          ( partition by x order by rowid ) rn
                              from t
                           )
                    where rn > 1 )

2. Delete

delete from t
  where rowid in ( select rid
                     from ( select rowid rid,
                                   row_number() over
                                          ( partition by x order by rowid ) rn
                              from t
                           )
                    where rn > 1 )
0
 
LVL 3

Expert Comment

by:cklautau
ID: 35488903
If all the columns are identical and you just want to get rid of 1 of the 2 rows, execute:
delete from table where name = 'Tom' and rownum=1;

That's it !
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35496378
- delete duplicate  in 1 statement:
delete from tblname where rowid not in (select max(rowid) from tblname group by columnA, columnB);

Open in new window



- you can also use any of the 4 methods on deleting duplicate records as shown in the following link:
http://www.orafaq.com/wiki/SQL_FAQ
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question