Q: How to delete multiple duplicate row?
--Find out no of duplicate rows
select q.qtxt_identifier,count(*) as cnt from question_text q
group by q.qtxt_identifier having count(*)>1 order by q.qtxt_identifier; --54 duplicate rows found
select sum(cnt) from( select q.qtxt_identifier,count(*) as cnt from question_text q
group by q.qtxt_identifier having count(*)>1 order by q.qtxt_identifier)A; --113
--So, I have to delete 113-54=59 rows
--Delete duplicate rows
DELETE FROM question_text WHERE rowid NOT IN
(SELECT max(rowid) FROM question_text GROUP BY qtxt_identifier); --59 deleted
Q: I have 2 same rows in a table. All fields are same. I have to keep 1 and delete 1.
select * from client c where c.clnt_number =332096; --2 same rows returns
DELETE FROM client
WHERE rowid =
(SELECT MIN(rowid)
FROM client
WHERE clnt_number =332096);
commit;
select * from client c where c.clnt_number =332096; --1 row returns
