How to delete duplicate rows in SQL server

Question

Hi, Suppose DataBase table has no primary key. How can we delete duplicate rows from table. What will be SQL statement? Thanks.

Aarti · Answer

Hi Alok, Please Check with below Sql to get duplicate rows according your question. create table #DuplicateTable ( id int , name varchar ( 20 )); insert into #DuplicateTable values ( 1 , 'a' ) ; insert into #DuplicateTable values ( 1 , 'a' ) ; insert into #DuplicateTable values ( 2 , 'b' ) ; insert into #DuplicateTable values ( 3 , 'c' ) ; insert into #DuplicateTable values ( 4 , 'd' ) ; insert into #DuplicateTable values ( 4 , 'd' ) ; insert into #DuplicateTable values ( 1 , 'e' ) ; select * from #DuplicateTable ---------------------------------------------- SELECT * INTO #duplicatedata FROM #DuplicateTable GROUP BY ID , name HAVING COUNT (*) > 1 select * from #duplicatedata ------------------------- -- now use below sql to delete duplicate rows DELETE FROM #DuplicateTable FROM #DuplicateTable INNER JOIN #duplicatedata ON #DuplicateTable . ID = #duplicatedata . ID AND #DuplicateTable . Name = #duplicatedata . Name ------select again main table and see duplicates deleted r not select * from #DuplicateTable select * from #Duplicatedata Thanks.

dev 0 · Answer

Hi Alok , As you mentioned without primary key in table, you can try below code, select * from temp_t ; select name , COUNT ( name ) as c , max ( id ) as m into #temp_new from temp_t group by name having COUNT ( name ) > 1; select * from temp_t where id not in ( select m from #temp_new ) order by name ; Hope this will help you . Thanks

Satyapriya Nayak · Answer

Hi Alok, STEP 1 - Insert your distinct data into a temporary table select distinct* into temp from TableName STEP 2 - Delete From TableName STEP 3 - Insert TableName Select * From temp Thanks

Pravin More · Answer

Hi Alok, do simple steps...... step1.select Distinct a.* into temp1 from tablename a step2. delete from tablename step3. insert temp1 to tablename like insert into tablename(column1,column2....) select * from temp1 Thanks, Pravin.

Vineet Kumar Saini · Answer

Hi Alok Ji..... Please visit this link for your problem http://stackoverflow.com/questions/985384/delete-duplicate-records-from-a-sql-table-without-a-primary-key http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/ Thanks..

Javeed M Shaikh · Answer

if there is a identifier in your table than you can first run this select statement and check: SELECT distinct A.id FROM table1 As A INNER JOIN table1 As B On (A.id = B.parentId); if it looks good than issue the delete for the same condition.