0

I am migrating data from one MySQL Database to a NoSQL DB (like Mongo or something similar). What I have is something like this:

 ------------------------
| id | lead |   event    |
 ------------------------
| 1  | 333  |  event1    |
 ------------------------
| 3  | 333  |  event2    |
 ------------------------
| 22 | 333  |  event3    |
 ------------------------
| 9  | 111  |  event1    |
 ------------------------

what I want to do is delete delete all entries except for the last entry. So after the delete command I would be left with:

 ------------------------
| id | lead |   event    |
 ------------------------
| 22 | 333  |  event3    |
 ------------------------
| 9  | 111  |  event1    |
 ------------------------

I would like to mention that it's against MySQL8.0 some of the other solutions I have looked at are 8+ years old and don't allow for some of the structures, while others don't work as expected, namely that they return all rows from the query

also I am using a "select" statement to see if I can return the values before I delete

Ctfrancia
  • 1,248
  • 1
  • 15
  • 39
  • Have you tried to select `MAX(id)` grouped by `lead`? You can then delete `where id not in ...` (as long as the id is a unique key). – Zacharya Haitin Jul 26 '22 at 13:19
  • `id` indicates a surrogate key, such as an identity column. Such surrogate keys are Not guaranteed to be in order and should Never be used for determining time series order. Always use another column for that; surrogate keys / identity columns should Only ever be used for unique identification of a row. – MatBailie Jul 26 '22 at 14:06

1 Answers1

1
DELETE t1 
FROM table t1 
JOIN table t2 USING (lead) 
WHERE t1.id < t2.id
Akina
  • 39,301
  • 5
  • 14
  • 25
  • is there a way that I could test this using the same `SELECT` statement to avoid importing the database multiple times? – Ctfrancia Jul 26 '22 at 14:19
  • 1
    @Ctfrancia Of course. Replace `DELETE t1` with `SELECT t1.*` - and you'll see the rows which will be removed. – Akina Jul 27 '22 at 04:17