--This script has been used to delete unwanted transactions for --https://redmine.cooperatic.fr/issues/4725 --Several variables must be set : --@nb : nb of transactions that should be removed --@date_before : time just before the first transaction we want to remove (ok if includes other transaction as soon as amount is different ; ok to include the first transaction (the true one)) --@date_after : time just after the last transaction we want to remove (ok if includes other transaction as soon as amount is different ; ok to include the first transaction (the true one)) --@montant : positive --set first name, last name and raison --CAREFUL ! Variable not working in limit clause : https://stackoverflow.com/questions/245180/using-variable-in-a-limit-clause-in-mysql --setting limit manualy is therefore REQUIRED in the two first DELETE FROM requests --It is probably better to identify the flux that have to be deleted and to deduce the operations that have to be deleted as well --instead of doing so, we sort the operations (prestataire and adherent) by flux_id before performing a DELETE LIMIT in order --to be sure to delete the operations that are associated to the same flux. --then we remove the flux --the drawback of this technique is that it does not make sure that the FIRST flux is the one that is kept if it was in the time interval set @nb = 42; set @date_before = 'aaaa-mm-dd HH:MM:SS'; set @date_after = 'aaaa-mm-dd HH:MM:SS'; set @montant = 4242.42; select @account_adherent := account_adherent.id from user inner join adherent on adherent.id = user.adherent_id inner join account_adherent on account_adherent.adherent_id = user.adherent_id where user.firstname = 'Prénom' and user.lastname = 'Nom'; select @account_prestataire := account_prestataire.id from prestataire inner join account_prestataire on account_prestataire.prestataire_id = prestataire.id where prestataire.raison = 'Kumbi' and currency = 'emlc'; DELETE FROM operation_adherent WHERE account_id = @account_adherent AND montant = -@montant AND created_at > @date_before AND created_at < @date_after ORDER BY flux_id DESC LIMIT 0; UPDATE account_adherent SET balance = balance + @nb*@montant WHERE id = @account_adherent; DELETE FROM operation_prestataire WHERE account_id = @account_prestataire AND montant = @montant AND created_at > @date_before AND created_at < @date_after ORDER BY flux_id DESC LIMIT 0; UPDATE account_prestataire SET balance = balance - @nb*@montant WHERE id = @account_prestataire; DELETE flux FROM flux LEFT JOIN operation_adherent oa ON oa.flux_id = flux.id WHERE oa.id IS NULL AND flux.type='adherent_prestataire';