mysql - 在 MySQL 中更新数据会出错

Question

update table1 
set isDeleted = 1 
where isDeleted = 0 
and mId in (select id from table1 where isDeleted = 1 );

错误代码:1093。您不能在 FROM 子句中指定要更新的目标表 'table1'

Answer 1

使用连接:

UPDATE table1 t1
JOIN table1 t2 ON t1.mId = t2.id
SET t1.isDeleted = 1
WHERE t1.isDeleted = 0
AND t2.isDeleted = 1