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
CreatedandEventNamein theSELECTandJOIN, 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