大家用mysql时,有没有遇到这样的问题
- 错误代码: 1093——You can’t specify target table ‘aa’ for update in FROM clause
(大意是:你不能从子句中更新目标表’aa’)
UPDATE
`aa`
SET
user_id_ = 'd0364fca2c7b4826a3b3e12ba6ac435d'
WHERE id_ IN
(SELECT
ma.`id_` AS id
FROM
`aa` ma
LEFT JOIN `product` p
ON ma.`product_id` = p.`id`
WHERE ma.`merchant_id_` != p.`merchant_id`)
上面是我的sql,子句是查出需要更新的id_范围。我在网上查了信息,mysql才会出现这样的问题,不能直接从本表中查出范围再更新本表。
解决办法就是把子句再嵌套一层,就OK了!
UPDATE
`aa`
SET
user_id_ = 'd0364fca2c7b4826a3b3e12ba6ac435d'
WHERE id_ IN
(SELECT
b.id
FROM
(SELECT
ma.`id_` AS id
FROM
`aa` ma
LEFT JOIN `product` p
ON ma.`product_id` = p.`id`
WHERE ma.`merchant_id_` != p.`merchant_id`) b)