MySQL "You can't specify target table 'X' for update in FROM clause" 错误解决方法

"You can't specify target table 'X' for update in FROM clause" 错误的意思，在官方文档有说明就是“你不能修改你从子查询出来的数据”。解决这个方法我们可以用子查询当临时表来解决。

Delete

DELETE ebayitem AS a FROM ebayitem AS a, 
 ( 
SELECT itemId, MAX(ebayId) AS ebayId  FROM ebayitem GROUP BY itemId HAVING COUNT(1) > 1
 ) AS b 
 WHERE a.itemId = b.itemId AND a.ebayId < b.ebayId

Update

update cargotype ct,(
select t.CargoTypeId,c.CargoTypeId as Pid
from cargoinfo c, cargotype t
where t.TypeName = c.Sku and t.Pid = 42 and c.CargoTypeId <> 42
) x
set ct.Pid = x.Pid
where ct.CargoTypeId = x.CargoTypeId

或

update cargotype ct INNER JOIN  cargoinfo c 
ON 
Ct.TypeName = c.Sku and Ct.Pid = 42 and c.CargoTypeId <> 42
set ct.Pid = c.CargoTypeId