mysql按日期删除,如何删除MySQL中的记录并保持最新日期

Example table_1

ID Email 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

解决方案

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