How Do I Remove Duplicates Rows In My Mysql Database? (keep The One With Lowest Primary Id)

Let's say I want to first select rows which have download_link the same. Then, I want to keep the one that has lowest primary id, and throw away the rest. Is there an easy SQL sta

Solution 1:

Something like this should work:

DELETEFROM `table` 
WHERE `id` NOTIN (
    SELECTMIN(`id`) 
    FROM `table`
    GROUPBY `download_link`)

Just to be on the safe side, before running the actual delete query, you might want to do an equivalent select to see what gets deleted:

SELECT*FROM `table` 
WHERE `id` NOTIN (
    SELECTMIN(`id`) 
    FROM `table`
    GROUPBY `download_link`)

Solution 2:

You don't need temporary tables or subqueries. You can do it with a simple join:

DELETE t0
FROM mytable AS t0
JOIN mytable AS t1 ON t1.download_link=t0.download_link AND t1.id<t0.id;

That is, “delete every row for which there is another row with the same link and a lower ID”.

Solution 3:

Error 1093 prevents your approach working in MySQL. Work-around by creating a temporary table:

CREATE TEMPORARY TABLE table_purge SELECTMIN(id) id FROMtableGROUPBY download_link;
  DELETEFROMtablewhere id NOTIN (SELECT id FROM table_purge);

Edited to add an alternative work-around that doesn't involve an explicit temporary table. Presumably this works because the query execution plan naturally creates a temporary table anyway:

DELETEtableFROMtableNATURALJOIN (
      SELECT id, download_link
      FROMtableNATURALJOIN (
          SELECTMIN(id) min_id, download_link
          FROMtableGROUPBY download_link ) table_min
      WHERE id > min_id
  )  table_to_purge;

Solution 4:

try following query

deletefromtablewhere id notin 
 (select*from 
      (selectmin(id) fromtablegroupby download_link)
 SWA_TABAL)

It works fine with mysql 5.0.x