How do I delete records in MySQL and keep the last

Question

Example table_1

ID Email         Answer   UpdateDate
1  [e-mail]   1        2011-07-02
2  [e-mail]   3        2011-07-11
3  [e-mail]   3        2011-07-12
4  [e-mail]   5        2011-07-13
5  [e-mail]   5        2011-07-14
6  [e-mail]   4        2011-07-14
7  [e-mail]   4        2011-07-14
8  [e-mail]   4        2011-07-15

How do I delete this records but keep the latest UpdateDate

And the result :

ID Email         Answer   UpdateDate
4  [e-mail]   5        2011-07-13
7  [e-mail]   4        2011-07-14
8  [e-mail]   4        2011-07-15

Answer 1

I'd check the UpdateDate against a correlated sub-query.

CREATE TEMPORARY TABLE
  latestRecord (
    Email        VARCHAR(128),
    updateDate   DATETIME
) 
INSERT INTO 
  latestRecord
SELECT
  Email,
  MAX(updateDate) AS updateDate
FROM
  table_1
GROUP BY
  Emal

DELETE 
  table_1
FROM
  table_1
INNER JOIN
  latestRecord
    ON  latestRecord.Email      = table_1.Email
    AND latestRecord.updateDate < table_1.updateDate

EDIT

Another refactor of the same logic

Answer 2

Make sure UpdateDate is a DATETIME field.

Do you want to keep the latest UpdateDate for each Answer value? This would do that:

delete from table_1 where UpdateDate not in ( select max(UpdateDate) from table_1 group by Answer );

if you only want to keep the latest date then:

delete from table_1 where UpdateDate not in ( select max(UpdateDate) from table_1 );

Answer 3

You could use a temporary variable to store the highest date and then a seperate query to delete everything that is < than that. Remember that variables are connection specific.

select max(UpdateDate) from table_1 into @TempUpdateDate

delete from table_1 where UpdateDate < @TempUpdateDate