duplicate records

ramji

11y1.1k

i want to remove records which are repeated more then once.

i have one table having three columns.In this table lot of duplicate records are there.

i want to delete all the duplicate records.for example i created one table i put

some sample records.please find the sample records.

id no sequence

35 35432 1

35 35432 2

35 35432 3

36 35432 1

i want to delete all the records of id=35.i want the record of only id=36.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

ramji11y ago
thanks
sanjay pawar11y ago
;WITH tempRecord AS
(select ROW_NUMBER() over(partition by column1, column2,column3)as RowNumber,
* from #tmp_tbl2)
delete tempRecord where Rownumber >1
ramji11y ago
is it possible to check both columns in where condition where id and no.because some times

id no sequence

35 35432 1
35 35432 2
35 35432 3
36 45232 1
35 44461 1

in this above example in this last row id value 35 is repeated but no column value is 44461 is different.so i do not want to delete that record. delete all the records which contains id and no
columns values are same.
Raja T11y ago
Hi, please check out bellow URLs

http://www.dotnet-tricks.com/Tutorial/sqlserver/IL3S290812-Remove-duplicate-records-from-a-table-in-SQL-Server.html

http://www.besttechtools.com/articles/article/sql-query-to-delete-duplicate-rows

http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server
Manas Mohapatra11y ago
Try below query:

DELETE FROM [TableName]
WHERE id IN(
SELECT id
FROM [TableName]
GROUP BY id
HAVING COUNT(id) > 1)
ramji11y ago
lot of duplicate records are there.so i cannot specify the id=35.for particular record.lot of ids are duplicated.so i want to delete all the records
Raja T11y ago
Hi Ramji, delete from tbl_name where id=35