I have written a query delete from Table1 where Tableid in(select Tableid from Table1 group by Tableid having count(*) >1) but this query removes all the data having count greater than 1.
Can someone help me with a single line query that deletes the duplicate data and resetting the count to 1.
I have table Table1
with
Tableid Count 1 10 2 2 3 1 4 NULL 5 31
Post Delete it should be
Tableid Count 1 1 2 1 3 1 4 NULL 5 1
Advertisement
Answer
I think this is what you are looking for
DECLARE @Table TABLE ( Name VARCHAR(20), Value INT ); ;WITH T AS ( SELECT CONCAT('a',1) AS Name, 1 AS Value UNION ALL SELECT CONCAT('a',T.Value + 1) AS Name, T.Value + 1 FROM T WHERE T.Value < 5 ) INSERT INTO @Table SELECT T.Name , T.Value FROM T INSERT INTO @Table ( Name, Value ) VALUES ( 'a5', -- Name - varchar(20) 5 -- Value - int ),( 'a5', -- Name - varchar(20) 5 -- Value - int ) INSERT INTO @Table SELECT * FROM @Table INSERT INTO @Table SELECT * FROM @Table SELECT COUNT(*) AS TotalCount , Name , Value FROM @Table GROUP BY Name , Value ORDER BY Name DELETE T FROM ( SELECT Name , Value , ROW_NUMBER() OVER(PARTITION BY Name, Value ORDER BY Value) AS RN FROM @Table ) AS T WHERE T.RN > 1 SELECT COUNT(*) AS TotalCount, Name, Value FROM @Table GROUP BY Name, Value ORDER BY Name, Value