一、问题描述:
今天更新某表时出现You can't specify target table '表名' for update in FROM clause这种错误,意即不能先select出同一表中的某些值,再update这个表(在同一语句中),即不能依据某字段值做判断再来更新某字段的值。
二、解决方案:
将SELECT出的结果再通过中间表SELECT一遍,具体可参考如下:
UPDATE `auto_picture_text_library` c -- 从图文库表中获取未使用的图文库id
SET c.`is_deleted` = 0,
c.`modify_time` = NOW( )
WHERE
c.id IN (
SELECT
d.id
FROM
(
SELECT
a.id
FROM
auto_picture_text_library a
LEFT JOIN auto_picture_text_library_page b ON a.id = b.library_id
WHERE
a.id IN ( 1, 2, 3 )
AND a.is_deleted = 0
AND a.company_id = 3
AND ( b.is_deleted = 1 OR b.page_id IS NULL )
) d
)