madhuk17

 Profile Answers by madhuk17 Questions by madhuk17

• Feb 8th, 2007

 

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);

sat.inn

 Profile Answers by sat.inn Questions by sat.inn

• Feb 22nd, 2007

 

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

Guest

• Feb 22nd, 2007

 

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

SANTO

• Mar 3rd, 2007

 

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);

suni123

 Profile Answers by suni123 

• Mar 5th, 2007

 

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.

Nikhil_4_Oracle

• Mar 12th, 2007

 

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.