CODEWITHSUNDEEP
mysqlsql
124697
124697

Reputation: 21893

How can I remove duplicate records and keep only 1 copy that has the latest dateUpdated field

I need to remove all duplicates records that have have the same stationId and only keep one record that has the latest dateUpdated

stationId is varchar(20) dateUpdated is datetime

I usually remove duplicates this the following, but this time I don't think it will work

ALTER IGNORE TABLE table ADD UNIQUE KEY idx1(title);

Upvotes: 0

Views: 46

Answers (2)

Danijel
Danijel

Reputation: 12689

DELETE t1 FROM table t1, table t2 WHERE t1.dateUpdated < t2.dateUpdated AND t1.stationId= t2.stationId

Delete all Duplicate Rows except for One in MySQL?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I don't think that alter table statement removes records; it just ignores index creation errors.

Instead:

delete t
    from table t left join
         (select t.stationId, max(t.dateUpdated) as maxdu
          from table t
          group by t.stationId
         ) tmax
         on t.stationId = tmax.stationId and t.dateUpdated = tmax.maxdu
    where tmax.stationId is null;

Upvotes: 1

Related Questions