MySQL delete duplicate records but keep latest

I have unique id and email fields. Emails get duplicated. I only want to keep one Email address o开发者_如何学Cf all the duplicates but with the latest id (the last inserted record).

How can I achieve this?

Imagine your table test contains the following data:

  select id, email
    from test;

ID                     EMAIL                
---------------------- -------------------- 
1                      aaa                  
2                      bbb                  
3                      ccc                  
4                      bbb                  
5                      ddd                  
6                      eee                  
7                      aaa                  
8                      aaa                  
9                      eee 

So, we need to find all repeated emails and delete all of them, but the latest id.
In this case, aaa, bbb and eee are repeated, so we want to delete IDs 1, 7, 2 and 6.

To accomplish this, first we need to find all the repeated emails:

      select email 
        from test
       group by email
      having count(*) > 1;

EMAIL                
-------------------- 
aaa                  
bbb                  
eee  

Then, from this dataset, we need to find the latest id for each one of these repeated emails:

  select max(id) as lastId, email
    from test
   where email in (
              select email 
                from test
               group by email
              having count(*) > 1
       )
   group by email;

LASTID                 EMAIL                
---------------------- -------------------- 
8                      aaa                  
4                      bbb                  
9                      eee                                 

Finally we can now delete all of these emails with an Id smaller than LASTID. So the solution is:

delete test
  from test
 inner join (
  select max(id) as lastId, email
    from test
   where email in (
              select email 
                from test
               group by email
              having count(*) > 1
       )
   group by email
) duplic on duplic.email = test.email
 where test.id < duplic.lastId;

I don't have mySql installed on this machine right now, but should work

Update

The above delete works, but I found a more optimized version:

 delete test
   from test
  inner join (
     select max(id) as lastId, email
       from test
      group by email
     having count(*) > 1) duplic on duplic.email = test.email
  where test.id < duplic.lastId;

You can see that it deletes the oldest duplicates, i.e. 1, 7, 2, 6:

select * from test;
+----+-------+
| id | email |
+----+-------+
|  3 | ccc   |
|  4 | bbb   |
|  5 | ddd   |
|  8 | aaa   |
|  9 | eee   |
+----+-------+

Another version, is the delete provived by Rene Limon

delete from test
 where id not in (
    select max(id)
      from test
     group by email)

Try this method

DELETE t1 FROM test t1, test t2 
WHERE t1.id > t2.id AND t1.email = t2.email

Correct way is

DELETE FROM `tablename`
  WHERE `id` NOT IN (
    SELECT * FROM (
      SELECT MAX(`id`) FROM `tablename`
        GROUP BY `name`
    ) 
  )

DELETE 
FROM
  `tbl_job_title` 
WHERE id NOT IN 
  (SELECT 
    * 
  FROM
    (SELECT 
      MAX(id) 
    FROM
      `tbl_job_title` 
    GROUP BY NAME) tbl)

revised and working version!!! thank you @Gaurav

If you want to keep the row with the lowest id value:

DELETE n1 FROM 'yourTableName' n1, 'yourTableName' n2 WHERE n1.id > n2.id AND n1.email = n2.email

If you want to keep the row with the highest id value:

DELETE n1 FROM 'yourTableName' n1, 'yourTableName' n2 WHERE n1.id < n2.id AND n1.email = n2.email

or this query might also help

DELETE FROM `yourTableName` 
  WHERE id NOT IN (
    SELECT * FROM (
      SELECT MAX(id) FROM yourTableName 
        GROUP BY name
    ) 
  )

I personally had trouble with the top two voted answers. It's not the cleanest solution but you can utilize temporary tables to avoid all the issues MySQL has with deleting via joining on the same table.

CREATE TEMPORARY TABLE deleteRows;
SELECT MIN(id) as id FROM myTable GROUP BY myTable.email;

DELETE FROM myTable
WHERE id NOT IN (SELECT id FROM deleteRows);

I must say that the optimized version is one sweet, elegant piece of code, and it works like a charm even when the comparison is performed on a DATETIME column. This is what I used in my script, where I was searching for the latest contract end date for each EmployeeID:

DELETE CurrentContractData
  FROM CurrentContractData
  INNER JOIN (
    SELECT
      EmployeeID,
      PeriodofPerformanceStartDate,
      max(PeriodofPerformanceEndDate) as lastDate,
      ContractID
    FROM CurrentContractData
    GROUP BY EmployeeID
    HAVING COUNT(*) > 1) Duplicate on Duplicate.EmployeeID = CurrentContractData.EmployeeID
    WHERE CurrentContractData.PeriodofPerformanceEndDate < Duplicate.lastDate;

Many thanks!

DELIMITER // 
CREATE FUNCTION findColumnNames(tableName VARCHAR(255))
RETURNS TEXT
BEGIN
    SET @colNames = "";
     SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.columns
        WHERE TABLE_NAME = tableName
        GROUP BY TABLE_NAME INTO @colNames;
    RETURN @colNames;
END // 
DELIMITER ;

DELIMITER // 
CREATE PROCEDURE deleteDuplicateRecords (IN tableName VARCHAR(255))
BEGIN
    SET @colNames = findColumnNames(tableName);
    SET @addIDStmt = CONCAT("ALTER TABLE ",tableName," ADD COLUMN id INT AUTO_INCREMENT KEY;");
    SET @deleteDupsStmt = CONCAT("DELETE FROM ",tableName," WHERE id NOT IN 
        ( SELECT * FROM ",
            " (SELECT min(id) FROM ",tableName," group by ",findColumnNames(tableName),") AS tmpTable);");
    set @dropIDStmt = CONCAT("ALTER TABLE ",tableName," DROP COLUMN id");

    PREPARE addIDStmt FROM @addIDStmt;
    EXECUTE addIDStmt;

    PREPARE deleteDupsStmt FROM @deleteDupsStmt;
    EXECUTE deleteDupsStmt;

    PREPARE dropIDStmt FROM @dropIDStmt;
    EXECUTE dropIDstmt;

END // 
DELIMITER ;

Nice stored procedure I created for deleting all duplicate records of a table without needing an existing unique id on that table.

CALL deleteDuplicateRecords("yourTableName");

I want to remove duplicate records based on multiple columns in table, so this approach worked for me,

Step 1 - Get max id or unique id from duplocate records

select *  FROM ( SELECT MAX(id) FROM table_name 
group by travel_intimation_id,approved_by,approval_type,approval_status having 
count(*) > 1

Step 2 - Get ids of single records from table

select *  FROM ( SELECT id FROM table_name 
group by travel_intimation_id,approved_by,approval_type,approval_status having 
count(*) = 1

Step 3 - Exclude above 2 queries from delete to

DELETE FROM `table_name` 
WHERE 
id NOT IN (paste step 1 query) a //to exclude duplicate records
and 
id NOT IN (paste step 2 query) b // to exclude single records

Final Query :-

DELETE FROM `table_name` 

WHERE id NOT IN (

select *  FROM ( SELECT MAX(id) FROM table_name 
group by travel_intimation_id,approved_by,approval_type,approval_status having 
count(*) > 1) a 
)
and id not in (

select *  FROM ( SELECT id FROM table_name 
group by travel_intimation_id,approved_by,approval_type,approval_status having 
count(*) = 1) b
);

By this query only duplocate records will delete.

Please try the following solution (based on the comments of the '@Jose Rui Santos' answer):

-- Set safe mode to false since;
-- You are using safe update mode and tried to update a table without a WHERE that uses a KEY column
SET SQL_SAFE_UPDATES = 0;

-- Delete the duplicate rows based on the field_with_duplicate_values 
-- Keep the unique rows with the highest id
DELETE FROM table_to_deduplicate
WHERE id NOT IN (
    SELECT * FROM (
        -- Select the highest id grouped by the field_with_duplicate_values
        SELECT MAX(id)
        FROM table_to_deduplicate
        GROUP BY field_with_duplicate_values
    )
    -- Subquery and alias needed since;
    -- You can't specify target table 'table_to_deduplicate' for update in FROM clause
    AS table_sub
);

-- Set safe mode to true
SET SQL_SAFE_UPDATES = 1;