PL/SQL - Suppose one table contain many identical records. Then how to delete only identical Records.

GeekInterview.com > Tech FAQs > PL/SQL

Go To First | Previous Question | Next Question

PL/SQL | Question 79 of 157

Suppose one table contain many identical records. Then how to delete only identical Records.

Total Answers and Comments: 6

Last Update: March 12, 2007 Asked by: globalanil18

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

February 08, 2007 10:59:57

madhuk17

Member Since: February 2007 Contribution: 15

RE: Suppose one table contain many identical records. ...

When a table contains identical records then we can delete them using the following query,lets take an example of emp tabledelete from emp awhere rowid in(select min(rowid) from emp bwhere a.empno=b.empno);

Is this answer useful? Yes | No

February 22, 2007 00:30:01

sat.inn

Member Since: February 2007 Contribution: 15

RE: Suppose one table contain many identical records. ...

hi its not working....it deletes both the duplicate row as well as non identical rows..

Is this answer useful? Yes | No

February 22, 2007 01:58:50

RE: Suppose one table contain many identical records. ...

hi its not working....it deletes both the duplicate row as well as non identical rows..

Is this answer useful? Yes | No

March 03, 2007 13:52:45

SANTO

RE: Suppose one table contain many identical records. ...

Hi,

you can check it out with this query to delete duplicate records.

suppose a table called temp contains identical rows in the column say 'name', then we can delete those rows with the following query.

DELETE FROM TEMP A
WHERE ROWID >(SELECT MIN(ROWID) FROM TEMP B
WHERE A.NAME = B.NAME);

Is this answer useful? Yes | No

March 05, 2007 14:27:12

suni123

Member Since: March 2007 Contribution: 1

RE: Suppose one table contain many identical records. ...

Hi,

To delete duplicate rows from table EMP

DELETE FROM EMP WHERE ROWID NOT IN
(SELECT MAX(ROWID) FROM EMP GROUP BY empno)

(OR)

DELETE FROM EMP WHERE ROWID NOT IN
(SELECT MIN(ROWID) FROM EMP GROUP BY empno)

Thanks,
Suneetha.

Is this answer useful? Yes | No

March 12, 2007 05:52:34

Nikhil_4_Oracle

RE: Suppose one table contain many identical records. ...

Hey,

AboveÂ Soln is not working with tables where data is like,

COL1
Â ----------
15
11 *
70
81
11 *
55
11 *
78

--Here 11 Repeats 3 times fine...

--Check d rowid
select rowid,col1 from nodup;

ROWIDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â COL1
------------------ ----------
AAAOV+AAAAAAAJ0AAAÂ Â Â Â Â Â Â Â 15
AAAOV+AAAAAAAJ0AABÂ Â Â Â Â Â Â Â 11Â --> Take this RowId
AAAOV+AAAAAAAJ0AACÂ Â Â Â Â Â Â Â 70
AAAOV+AAAAAAAJ0AADÂ Â Â Â Â Â Â Â 81
AAAOV+AAAAAAAJ0AAEÂ Â Â Â Â Â Â Â 11
AAAOV+AAAAAAAJ0AAFÂ Â Â Â Â Â Â Â 55
AAAOV+AAAAAAAJ0AAGÂ Â Â Â Â Â Â Â 11
AAAOV+AAAAAAAJ0AAHÂ Â Â Â Â Â Â Â 78

--Now Delete Dup val.

delete from nodup
where col1=11
And
Rowid <> 'AAAOV+AAAAAAAJ0AAB';

This wll Rocks Honey.......

Bye

Nikhil.

Is this answer useful? Yes | No

Go To Top