CODEWITHSUNDEEP
sqlmysqldatabaseunique
TIMEX
TIMEX

Reputation: 272184

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 statement for this? Would this work?

delete from mytable
where id not in
    (select min(id)
     from mytable
     group by download_link);

Upvotes: 2

Views: 1326

Answers (4)

bobince
bobince

Reputation: 536615

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”.

Upvotes: 2

Romani
Romani

Reputation: 3241

try following query 

 delete from table where id not in 
 (select * from 
      (select min(id) from table group by download_link)
 SWA_TABAL)

It works fine with mysql 5.0.x

Upvotes: 0

hash1baby
hash1baby

Reputation: 131

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

  CREATE TEMPORARY TABLE table_purge SELECT MIN(id) id FROM table GROUP BY download_link;
  DELETE FROM table where id NOT IN (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:

 DELETE table
  FROM table 
  NATURAL JOIN (
      SELECT id, download_link
      FROM table
      NATURAL JOIN (
          SELECT MIN(id) min_id, download_link
          FROM table
          GROUP BY download_link ) table_min
      WHERE id > min_id
  )  table_to_purge;

Upvotes: 1

tdammers
tdammers

Reputation: 20721

Something like this should work:

DELETE FROM `table` 
WHERE `id` NOT IN (
    SELECT MIN(`id`) 
    FROM `table`
    GROUP BY `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` NOT IN (
    SELECT MIN(`id`) 
    FROM `table`
    GROUP BY `download_link`)

Upvotes: 4

Related Questions