7ma4XxH0

1DROP TABLE IF EXISTS payments_uniq_keys_to_delete; 
2CREATE TEMPORARY TABLE payments_uniq_keys_to_delete AS ( SELECT concat(AMOUNT, DATE, DIRECTION, REFERENCE) AS uniq_key 
3FROM payments 
4GROUP BY AMOUNT, DATE, DIRECTION, REFERENCE HAVING count(*) > 1 ); 
5DROP TABLE IF EXISTS payments_min_IDs_to_keep; 
6
7CREATE TEMPORARY TABLE payments_min_IDs_to_keep AS ( SELECT min(ID) AS min_ID, count(*) AS cnt, GROUP_CONCAT(ID) AS IDs 
8FROM payments 
9GROUP BY AMOUNT, DATE, DIRECTION, REFERENCE HAVING count(*) > 1 ); 
10
11delete FROM payments 
12where concat(AMOUNT, DATE, DIRECTION, REFERENCE) in ( SELECT uniq_key FROM payments_uniq_keys_to_delete ) 
13and ID not in ( SELECT min_ID FROM payments_min_IDs_to_keep );