We help IT Professionals succeed at work.

select the row with duplicated value from oracle table

keepworking
keepworking asked
on
Medium Priority
409 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
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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 !
Top Expert 2011

Commented:
- 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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.