MySQL DELETE条件使用SELECT结果

前段时间要写一个根据查询结果删除条目的SQL，踩到了MySQL的一个坑，在此记录一下

先放正确写法

DELETE FROM mei_tuan_order_goods WHERE id IN(
	SELECT MAX(a.id) AS id FROM (
		SELECT a.id FROM mei_tuan_order_goods AS a
		JOIN `mei_tuan_order_main` AS b ON b.id = a.`pid`
		WHERE b.order_time >= '2021-05-30 17:32:03'	
	) AS a GROUP BY a.id
)

再来看看错误写法，这样会报错

DELETE FROM mei_tuan_order_goods WHERE id IN(
	SELECT a.id FROM mei_tuan_order_goods AS a
		JOIN `mei_tuan_order_main` AS b ON b.id = a.`pid`
		WHERE b.order_time >= '2021-05-30 17:32:03'	
)

错误信息

1 queries executed, 0 success, 1 errors, 0 warnings

查询：DELETE FROM mei_tuan_order_goods WHERE id IN( SELECT a.id FROM mei_tuan_order_goods AS a JOIN `mei_tuan_order_main` AS b ON b.id...

错误代码： 1093
You can't specify target table 'mei_tuan_order_goods' for update in FROM clause

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0.079 sec