3

In my mysql database I have a table similar to this one:

Data                |Val  | Val2 
2016-11-11 14:48:38 |10   | 20
2016-11-11 14:48:38 |30   | 40
2016-11-06 11:48:38 |50   | 60
2016-11-08 19:48:38 |70   | 80
2016-11-06 11:48:38 |90   |100
2016-11-16 10:13:37 |100  |110
2016-11-16 10:13:37 |120  |130
2016-11-16 10:16:43 |140  |150
2016-11-16 10:13:37 |140  |150

Sometimes in my database I can have duplicate dates (data) but different values for (val,val2), I wish to delete all the rows with duplicate data except the last one inserted on the database. How can I achieved that? For example, for the table above the result that I want is:

Data             |Val |Val2
11/11/2016 14:48 |30  |40
08/11/2016 19:48 |70  |80
06/11/2016 11:48 |90  |100
16/11/2016 10:16 |140 |150
16/11/2016 10:13 |140 |150

I was using sqlite3 and I could achieve what I want by doing

delete from table where rowid not in (select max(rowid) from table group by data)

but I couldn't find the equivalent for mysql.

eriast
  • 35
  • 4
  • You could find the answer here http://stackoverflow.com/questions/2728413/equivalent-of-oracle-s-rowid-in-mysql – Oscar Gallardo Nov 16 '16 at 15:44
  • the thing id you're using rowid, so if the table has a primary key you could use it to get the same effect – Oscar Gallardo Nov 16 '16 at 15:45
  • 1) SELECT & remember the row that you want, 2) DELETE all rows, 3) INSERT the saved row? It's not pretty, but ... – Mawg says reinstate Monica Nov 16 '16 at 15:46
  • If you have duplicated `dates` how you know which one is the latest? do you mean keep the biggest value? – Juan Carlos Oropeza Nov 16 '16 at 15:48
  • Looks like your question has been answered, but I feel like I should just throw this out there: 1) place a unique key on the date so that duplicates do not get inserted in the first place 2) if it's the last one you want, use an upsert strategy whenever you want to add a new row to update the existing one if it exists or insert it otherwise. Information on upsert in sqlite: http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace – Blake O'Hare Nov 16 '16 at 16:02

1 Answers1

2

This will delete the smallest Val when have same date

SQL DEMO

DELETE t1 
FROM Table1 t1
JOIN Table1 t2
  ON t1.`Data` = t2.`Data`
 AND t1.`Val` < t2.`Val`;

OUTPUT

enter image description here

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118