In MySQL, you can't modify the same table which you use in the SELECT part. SQL 语句求解答

[cctecoo]

假设有consume表,其中示例数据如下:

id	money	flag	history_id
1	10	1	10
2	20	2	20
3	20	2	20
4	10	2	30

将含有重复history_id的记录找出,然后update money字段值除以2.
但是执行sql语句报错.Error Code: You can't specify target table 'consume' for update in FROM clause

sql语句如下:

UPDATE consume
SET 
    money = money / 2
where
    history_id in (SELECT 
            history_id
        FROM
            consume
        where
            flag = 2
        group by history_id
        having count(history_id) > 1);

请问怎么修改sql语句,求解决方法.

[rqrq]

UPDATE consume AS a INNER JOIN (
SELECT history_id FROM consume WHERE flag = 2 GROUP BY history_id HAVING COUNT(history_id) > 1
) AS b ON a.history_id = b.history_id SET money = money / 2