Delete duplicated old rows and keep only latest mysql

Question

I have a table

id:name:passportnumber:score
1:xxx:123456:99
2:yyy:789012:88
3:xxx:123456:11
4:xxx:123456:44
5:xxx:123456:66
6:xxx:123456:77

I want to delete duplicated old rows and keep only latest one which has same passport number

id:name:passportnumber:score    
2:yyy:789012:88
6:xxx:123456:77

What is the best way without temp table

Thanks

Taryn · Answer 1 · 2013-04-30T18:06:54.190

6

You should be able to do a self-join to your table and perform the delete:

delete t1
from yt t1
inner join yt t2
  on t1.passportnumber = t2.passportnumber
where t1.id < t2.id;

edited Apr 30 '13 at 18:06

answered Apr 30 '13 at 18:01

Taryn

Question out of interest, is MySQL smart enough to not make this an almost cartesian product with one row (1<2), one row (1<3), one row (2<3), one row (1<4), one row (2<4) etc? – Joachim Isaksson Apr 30 '13 at 18:27
@sgeddes It's pretty good, but [this](http://stackoverflow.com/questions/6841333/why-is-subtracting-these-two-times-in-1927-giving-a-strange-result/6841479#6841479) is the best answer *ever* posted. – Kermit Apr 30 '13 at 18:40
@FreshPrinceOfSO -- :-) Touché! – sgeddes Apr 30 '13 at 18:42

score 1 · Answer 2 · answered Apr 30 '13 at 18:02

1

A LEFT JOIN on your DELETE can do it;

DELETE Table1 FROM Table1
LEFT JOIN Table1 b
  ON Table1.id<b.id 
 AND Table1.passportnumber=b.passportnumber
WHERE b.id IS NOT NULL;

answered Apr 30 '13 at 18:02

Joachim Isaksson

2 Answers2