Query to remove the duplicate rows from the table

Gurucharan · New User Joined: 27 Feb 2004 Posts: 4

hi folks,

Pls. help me in writing queries. How to remove the duplicate rows from the
table and also to delete the duplicate rows from the table.

mvs_butta · New User Joined: 23 Dec 2003 Posts: 13

Hop this will help u

table temp with col as id and having values

2
3
4
3
2
5
6

delete from temp a
where a.id in (select b.id from temp b
where a.id = b.id group by b.id having count(*) > 1)

will give you

4
5
6

Gurucharan · New User Joined: 27 Feb 2004 Posts: 4

Hi folks,

I need the first occurence of the duplicate.

2
3
2
2
4
3
5
6

i need output like this:

2
3
4
5
6

ConnoisseuR · New User Joined: 06 Aug 2004 Posts: 8 Location: india

you can use DISTINCT key word to eliminate duplicate records.all yu'll get will just be the first occurance of the duplicate record.
dp i make myself clear.

karthi_ind · Posted: Fri Nov 05, 2004 1:20 pm

hi

the answer for the abve ques?

delete from temp whre id in

ksivapradeep · New User Joined: 30 Jul 2004 Posts: 95

hi karthi,

ur query giving -118 error(the insert, update, insert,delete referensing same table in from clause).i tried with range variables but i am not getting plz check it out once again.

regards,
siva pradeep