CODEWITHSUNDEEP
mysql
Marc
Marc

Reputation: 9527

PHP SQL - Advanced delete query

I have a table with 3 columns: id, date and name. What I am looking for is to delete the records that have a duplicate name. The rule should be to keep the record that has the oldest date. For instance in the example below, there is 3 records with the name Paul. So I would like to keep the one that has the oldest date (id=1) and remove all the others (id = 4 and 6). I know how to make insert, update, etc queries, but here I do not see how to make the trick work. 

id, date, name

1, 2012-03-10, Paul
2, 2012-03-10, James
4, 2012-03-12, Paul
5, 2012-03-11, Ricardo
6, 2012-03-13, Paul

mysql_query(?);

Upvotes: 1

Views: 546

Answers (6)

wildplasser
wildplasser

Reputation: 44240

DELETE FROM thetable tt
WHERE EXISTS (
    SELECT *
    FROM thetable tx
    WHERE tx.thename = tt.thename
    AND tx.thedate > tt. thedate
    );

(note that "date" is a reserver word (type) in SQL, "and" name is a reserved word in some SQL implementations)

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

DELETE t
FROM tableX AS t
  LEFT JOIN
    ( SELECT name
           , MIN(date) AS first_date
      FROM tableX
      GROUP BY name
    ) AS grp
    ON  grp.name = t.name
    AND grp.first_date = t.date
WHERE
    grp.name IS NULL

Upvotes: 0

Matt Fellows
Matt Fellows

Reputation: 6522

Something like this would work:

DELETE FROM tablename WHERE id NOT IN (
    SELECT tablename.id FROM (
        SELECT MIN(date) as dateCol, name FROM tablename GROUP BY name /*select the minimum date and name, for each name*/
    ) as MyInnerQuery 
    INNER JOIN tablename on MyInnerQuery.dateCol = tablename.date 
        and MyInnerQuery.name = tablename.name /*select the id joined on the minimum date and the name*/
) /*Delete everything which isn't in the list of ids which are the minimum date fore each name*/

Upvotes: 1

pritaeas
pritaeas

Reputation: 2093

Like Matt, but without the join:

DELETE FROM `table` WHERE `id` NOT IN (
    SELECT `id` FROM (
        SELECT `id` FROM `table` GROUP BY `name` ORDER BY `date`
    ) as A 
)

Without the first SELECT you will get "You can't specify target table 'table' for update in FROM clause"

Upvotes: 1

Starx
Starx

Reputation: 78971

The best suggestion I can give you is create a unique index on name and avoid all the trouble.

Follow the steps as Peter Kiss said from 2 to 3. Then do this

ALTER Table tablename ADD UNIQUE INDEX name (name)

Then Follow 4 Insert everything from the temporary table to the original.

All the new duplicate rows, will be omitted

Upvotes: 1

Peter Kiss
Peter Kiss

Reputation: 9319

  1. Select all the records what you want to keep
  2. Insert them to a temporary table
  3. Delete everything from the original table
  4. Insert everything from the temporary table to the original

Upvotes: 1

Related Questions