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 );
精彩评论