开发者

How do I delete records in MySQL and keep the lastest date

Example table_1

ID Em开发者_如何学Goail         Answer   UpdateDate
1  xxx.@xx.com   1        2011-07-02
2  xxx.@xx.com   3        2011-07-11
3  vvv.@xx.com   3        2011-07-12
4  vvv.@xx.com   5        2011-07-13
5  xxx.@xx.com   5        2011-07-14
6  xxx.@xx.com   4        2011-07-14
7  xxx.@xx.com   4        2011-07-14
8  zzz.@xx.com   4        2011-07-15

How do I delete this records but keep the latest UpdateDate

And the result :

ID Email         Answer   UpdateDate
4  vvv.@xx.com   5        2011-07-13
7  xxx.@xx.com   4        2011-07-14
8  zzz.@xx.com   4        2011-07-15


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


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


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 );
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜