Delete all duplicated entries where multiple columns duplicates match

Question

I need to add this contrain to my table called 'kraken'

ALTER TABLE kraken ADD UNIQUE unique_index(exchange_pair,timestamp);

this shows me that i have duplicated entries on those 2 columns (exchange_pair and timestamp) so first i have to delete all the duplicated entries based on those 2 columns

this is what i found but im not sure if my query would work since i need to delete only duplicated entris with column 'exchange_pair' and 'timestamp', i dont want to mess things up with my table https://www.mysqltutorial.org/mysql-delete-duplicate-rows/

Answer 1

The easiest way to remove duplicates is to join the table with itself:

DELETE z.* 
  FROM kraken AS w
  JOIN kraken AS z ON z.exchange_pair = w.exchange_pair
                  AND z.timestamp = w.timestamp
                  AND z.id > w.id -- or what primary key you have
;

All duplicates will be deleted except one with the lowest id.