MySQL之You can‘t specify target table for update in FROM clause解决办法

需求：修改omssku表部分特定行 defaultSku的值

SELECT a.id from omssku a LEFT JOIN omsitem b

on a.itemId =b.id

where b.itemName like "perfectitem%"

错误样例：

UPDATE omssku set omssku.defaultSku=1

where omssku.id in

(SELECT a.id from omssku a LEFT JOIN omsitem b

on a.itemId =b.id

where b.itemName like "perfectitem%")

mysql不允许，修改当前表时，又直接以当前表作为查询条件

解决方法一：加中间表

UPDATE omssku

SET omssku.defaultSku = 0

WHERE

omssku.id IN (

SELECT

c.id

FROM

(SELECT a.id FROM omssku a LEFT JOIN omsitem b ON a.itemId = b.id WHERE b.itemName LIKE "perfectitem%" ) c

)

解决方法二：查询结果与原表连接作为新表

UPDATE omssku o,

(SELECT a.id as id

FROM

omssku a

LEFT JOIN omsitem b ON a.itemId = b.id

WHERE

b.itemName LIKE "perfectitem%") u

SET o.defaultSku = 1

WHERE

o.id = u.id

解决方法三：思想与方法二一致

UPDATE omssku a

INNER JOIN omsitem b ON a.itemId = b.id

SET a.defaultSku = 1

WHERE

b.itemName LIKE "perfectitem%";