CODEWITHSUNDEEP
mysqlsqlcountsql-deletehaving
User008
User008

Reputation: 387

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

Upvotes: 1

Views: 73

Answers (6)

Aasish Kr. Sharma
Aasish Kr. Sharma

Reputation: 556

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

Upvotes: 1

JYoThI
JYoThI

Reputation: 12085

try this

 DELETE FROM Table1 
       WHERE Tableid IN (SELECT Tableid FROM Table1 GROUP BY Tableid HAVING COUNT(*) > 1)

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can also set a uniquie key on the field where are make the row unique with the KEYWORD IGNORE.

It will vreate a unique key and deletes direct the duplications.

ALTER IGNORE TABLE mytable
ADD UNIQUE KEY (Tableid);

Upvotes: 0

rhavendc
rhavendc

Reputation: 1013

To delete all the duplicate data: Group the column that may have the same data.

DELETE FROM table 
WHERE id IN (SELECT id FROM table GROUP BY column HAVING COUNT(column) > 1)

To delete the duplicate and keep one of it: Get at least (1) data from the duplicate and grouped column.

DELETE t1 FROM table t1, table t2 
WHERE t1.id <> t2.id AND t1.column = t2.column

Back-up your data first before testing anything.

Upvotes: 1

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

i would suggest to use select query here instead of delete

select tableId,case when count >1 then (count-(count-1)) 
                    else count
               end as count
from table 1;

Upvotes: 0

Captain Catz
Captain Catz

Reputation: 96

  1. Sort the data alphabetically or numerical order.
  2. Check if current field is equal to previous field.
  3. Delete if the same.

Upvotes: 0

Related Questions