0

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);
124697
  • 21,137
  • 63
  • 182
  • 307

2 Answers2

1

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;
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
0
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?

Community
  • 1
  • 1
Danijel
  • 11,920
  • 4
  • 35
  • 53