Store procedures for delete a repeat records

Hello i have a table in MySQL:

“contracts” where i create a 3 record’s for each client_id, but one bug in the backend create more records on some client_id i try create a store procedure to delete repeat records, example for 3 records:

ID cliente_id amount_preapprove
1     1        10000
2     1        12000
3     1        14000
4     1        10000 // <- record repeat 
5     1        10000 // <- record repeat 
6     1        14000 // <- record repeat 
7     2        7000 
8     2        8000 
9     2        9000 
10    2        7000 // <- record repeat for client_id 2
11    2        8000 // <- record repeat 
12    2        9000 // <- record repeat 
... N Clients repeat

i need get distinc amount_preapprove for each user, truncate the table and insert result in the store procedures?

I only need a 3 records for each client

Answer

I am unsure whether you really want to retain 3 records per client. In your sample data, it looks more like you are trying to delete duplicates on (cliente_id, amount_preapprove).

If so, you could use the following query:

delete t
from mytable t
inner join mytable t1 
    on  t1.cliente_id = t.client_id
    and t1.amount_preapprove = t.amount_preapprove
    and t1.id < t.id

This query will delete every record for which another record exists with the same cliente_id and amount_preapprove but a smaller id. In other words, this deletes duplicates while retaining the record that has the smallest id.