phpmyadmin Question posted in
The official documentation can be found here.

Delete all duplicate rows in mysql – Phpmyadmin

i have MySQL data which is imported from csv file and have multiple duplicate files on it,

I picked all non duplicates using Distinct feature.

Now i need to delete all duplicates using SQL command.

Note i don’t need any duplicates i just need to fetch only noon duplicates

thanks.

for example if number 0123332546666 is repeated 11 time i want to delete 12 of them.

Mysql table format

ID, PhoneNumber
Tags:

3

Answers


  1. 0

    you could try using a left join with the subquery for min id related to each phonenumber ad delete where not match

    delete m 
from m_table m
left join  (
    select min(id), PhoneNumber
    from m_table  
    group by PhoneNumber
) t on t.id = m.id 
where t.PhoneNumber is null

    otherwise if you want delete all the duplicates without mantain at least a single row you could use

    delete m 
from m_table m
INNER join  (
    select PhoneNumber
    from m_table  
    group by PhoneNumber
    having count(*) > 1
) t on t.PhoneNumber= m.PhoneNumber
    Login or Signup to reply.
  2. 0

    Just COUNT the number of duplicates (with GROUP BY) and filter by HAVING. Then supply the query result to DELETE statement:

    DELETE FROM Table1 WHERE PhoneNumber IN (SELECT a.PhoneNumber FROM (
  SELECT COUNT(*) AS cnt, PhoneNumber FROM Table1 GROUP BY PhoneNumber HAVING cnt>1
) AS a);

    http://sqlfiddle.com/#!9/a012d21/1

    complete fiddle:
    schema:

    CREATE TABLE Table1
    (`ID` int, `PhoneNumber` int)
;
    
INSERT INTO Table1
    (`ID`, `PhoneNumber`)
VALUES
    (1, 888),
    (2, 888),
    (3, 888),
    (4, 889),
    (5, 889),
    (6, 111),
    (7, 222),
    (8, 333),
    (9, 444)
;

    delete query:

    DELETE FROM Table1 WHERE PhoneNumber IN (SELECT a.PhoneNumber FROM (
  SELECT COUNT(*) AS cnt, PhoneNumber FROM Table1 GROUP BY PhoneNumber HAVING cnt>1
) AS a);
    Login or Signup to reply.
  3. 0

    Instead of deleting from the table, I would suggest creating a new one:

    create table table2 as 
    select min(id) as id, phonenumber
    from table1
    group by phonenumber
    having count(*) = 1;

    Why? Deleting rows has a lot of overhead. If you are bringing the data in from an external source, then treat the first landing table as a staging table and the second as the final table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search