How to delete only the one row from two identical rows

[srinivasm]

Hi

How can we delete the duplicate values from a table?

normally in a table if we want to know what are all the duplicate values of a single column, I have tried like this

Table: user_info

userid -- password -- age
a --- p -- 26
b --- p -- 24
c --- p -- 16
d --- p -- 46
e --- p -- 42
a --- p -- 26
b --- p -- 24


select userid,count(userid) from user_info group by userid having count(userid) > 1;

result:

userid -- count(userid)
a 2
b 2

And i have used the above query to delete the duplicate rows like

delete from user_info where userid in (select userid,count(userid) from user_info group by userid having count(userid) > 1);

then it is deleting all the 4 rows. But i wanted to delete only only the two rows from the 4 identical rows. It should delete any of the two rows out of 4 rows.


Thanks for any help

Srinivas M

[balajiyes]

hi,
try this:
delete from table a where rowid > (
select min(rowid) from table b where
a.column1 = b.column2);

[vijayr]

Hi Srini,

You can use any one of the following queries.

delete from TABLENAME where rowid in
(select min(rowid) from TABLENAME group by COLNAMES
having count(*) > 1);

or

delete from TABLENAME where rowid in
(select max(rowid) from TABLENAME group by COLNAMES
having count(*) > 1);

Hope this will help you.