MySQL 中 You can't specify target table '表名' for update in FROM clause错误解决办法

在MySQL中，写SQL语句的时候 ，可能会遇到You can’t specify target table ‘表名’ for update in FROM clause这样的错误，它的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值。

解决方式：
可以参见国外的博客： https://stackoverflow.com/questions/51087937/on-update-mysql-row-you-cant-specify-target-table-x-for-update-in-from-claus

update x set available_material_id = null where id not in (select id from x where additional_info = 1);

– 使用left join 改写
update x left join
x xx
on x.id = xx.id and xx.additional_info = 1
set available_material_id = null
where xx.id is null;

例子：
UPDATE c_device_workstatus SET ex_id=‘20191009’
WHERE c_device_workstatus.uid in
(SELECT c.uid
FROM c_device_workstatus AS c
INNER JOIN p_rel_orgenable AS pro ON c.org_code=pro.org_code AND pro.status=‘A’
WHERE c.status=‘A’ AND c.org_code LIKE ‘86.32%’ AND work_status=0)

报错：
[Err] 1093 - You can’t specify target table ‘c_device_workstatus’ for update in FROM clause

改为：
UPDATE c_device_workstatus AS c INNER JOIN
p_rel_orgenable AS pro ON c.org_code=pro.org_code AND pro.status=‘A’
AND c.status=‘A’ AND c.org_code LIKE ‘86.32%’ AND work_status=0
SET c.ex_id=‘20191009’