(1093, u"You can't specify target table 'wallet_consume_record' for update in FROM clause") when using delete in mysql

Question

I want to delete dulplicate data from MySQL 5.7,this is my sql:

DELETE FROM wallet_consume_record 
WHERE trans_no IN (
        SELECT trans_no 
        FROM wallet_consume_record
        GROUP BY trans_no
        HAVING count(trans_no) > 1)
AND id NOT IN (
    SELECT min(id)
    FROM wallet_consume_record
    GROUP BY trans_no
    HAVING count(trans_no) > 1
);

but when I execute it, it throws:

(1093, u"You can't specify target table 'wallet_consume_record' for update in FROM clause")

I have already tried:

SET optimizer_switch = 'derived_merge=off';

why and how to make it work? I also tried to wrap:

DELETE FROM wallet_consume_record 
WHERE trans_no IN (
        SELECT trans_no 
        FROM wallet_consume_record
        GROUP BY trans_no
        HAVING count(trans_no) > 1)
AND id NOT IN (
    select c.id from 
    (
        SELECT min(id) as id
        FROM wallet_consume_record
        GROUP BY trans_no
        HAVING count(trans_no) > 1
    ) as c
);

still not work.

mysql [email protected]:meow_report_pro> select version();
+-------------+
| version()   |
|-------------|
| 5.7.29-log  |
+-------------+
1 row in set
Time: 0.001s

Answer 1

MySQL doesn't accept refering to the table being updated or deleted in the from clause of the same query.

You could express this with a join instead:

delete w
from wallet_consume_record w
inner join (
    select trans_no, min(id) min_id
    from wallet_consume_record
    group by trans_no
    having count(*) > 1
) w1 on w.trans_no = w1.trans_no and w.id > w1.min_id