Thread: How can we delete the duplicate data from a database table?

Kindly post what you have tried so far for further discussion on the topic.

;with DelDup as (select row_number() over (partition by
ID order by ID) as RowNo from duplicateTest )
Delete from DelDup where RowNo> 1

DELETE FROM emp WHERE rowid NOT IN (SELECT max(rowid) FROM emp GROUP BY id);

To delete the duplicate data you can use this sql query: method 1 :delete from categories where categories.id not in (select min(c.id) from categories as c where categories.category_name = c.category_name) method 2: set rowcount syntax for set rowcount is as follows: set rowcount { number | @number_var } set rowcount 3 delete from users where firstname = n'elvis' and lastname = n'presley' set rowcount 0 -- (3 row(s) affected)

Originally Posted by nageshkota

To delete the duplicate data we may use the temporory tables, but with out using any teporory tables can we delete the duplicated data, if how it is possible?

DELETE FROM wikis_details
WHERE wiki_id in
(SELECT wiki_id
FROM wikis_details
GROUP BY wiki_id
HAVING count(wiki_id)>1)

using rowid

We can use Common table Expression

;With empcte(id,sal,Ranking)
as
(Select id,sal,Ranking=Dense_Rank() over (Partition by id,sal
order by newid() asc) from employee)
Delete * from empcte where Ranking>1

Regards
Smitha