User008 User008 - 2 months ago 6
MySQL Question

I want a single query that deletes duplicate entry from my database

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




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 * FROM @Table ORDER BY Name, Value