Clean table for duplicates and keep the newest

We got an table with timestamps, coords, ids and events. I'd like to clean my table in the way so that one duplicate of each coordinate (X, Y, Z) is kept (the newest one with place as event, cleaning all break events).

decayworld (table):

+------------+-----------+-----+----+-----+------+
| Created    | EventName | X   | Y  | Z   | id   |
+------------+-----------+-----+----+-----+------+
| 1515700748 | break     | -64 | 82 | 229 | 4110 |
| 1515700749 | place     | -64 | 82 | 229 | 4111 |
| 1515700750 | break     | -64 | 82 | 229 | 4112 |
| 1515700750 | place     | -64 | 82 | 229 | 4113 |
| 1515700751 | break     | -64 | 82 | 229 | 4114 |
| 1515700751 | place     | -64 | 82 | 229 | 4115 |
+------------+-----------+-----+----+-----+------+

What's wrong with the following code?

DELETE from decayworld where EventName='break';

DELETE a
FROM decayworld a
LEFT JOIN
(
  SELECT MAX(id) id, Created, EventName, X, Y, Z
 FROM decayworld
  GROUP BY X, Y, Z
) b ON a.Created = b.Created AND
a.X = b.X and
a.Y = b.Y AND
a.Z = b.Z AND
a.EventName = b.EventName and 
a.id = b.id
WHERE b.ID IS NULL;

select * from decayworld;

Result (the issue): All rows get cleaned.

Desired output:

+------------+-----------+-----+----+-----+------+
| Created    | EventName | X   | Y  | Z   | id   |
+------------+-----------+-----+----+-----+------+
| 1515700751 | place     | -64 | 82 | 229 | 4115 |
+------------+-----------+-----+----+-----+------+

1 answer

  • answered 2018-01-11 20:20 Barmar

    By including Created and EventName in the SELECT and JOIN, those columns may be coming from different rows than the maximum of the group, so the join might not find anything that matches all criteria. The proper way is:

    DELETE a
    FROM decayworld AS a
    JOIN (SELECT X, Y, Z, MAX(id) AS id
          FROM decayworld
          GROUP BY X, Y, Z) AS b
    ON a.X = b.X
    AND a.Y = b.Y
    AND a.Z = b.Z
    AND a.id < b.id