CODEWITHSUNDEEP
mysql
Ctfrancia
Ctfrancia

Reputation: 1588

Mysql, delete all except for last entry

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

Upvotes: 0

Views: 52

Answers (1)

Akina
Akina

Reputation: 42844

DELETE t1 
FROM table t1 
JOIN table t2 USING (lead) 
WHERE t1.id < t2.id

Upvotes: 1

Related Questions