报错写法
报错写法
UPDATE users
SET vip = 1
WHERE id IN (SELECT id FROM users WHERE amount > 1000);
-- ERROR 1093 (HY000): You can't specify target table 'users' for update in FROM clause改写
改写
-- 派生表物化一层
UPDATE users
SET vip = 1
WHERE id IN (SELECT id FROM (SELECT id FROM users WHERE amount > 1000) AS x);
-- 或 JOIN 改写
UPDATE users u
JOIN (SELECT id FROM users WHERE amount > 1000) AS x ON x.id = u.id
SET u.vip = 1;| 原因 | MySQL 限制更新目标表不能在子查询中直接出现（防止同一语句读写冲突） |
|---|---|
| PostgreSQL | 无此限制，子查询按语句快照读取，可正常执行 |
| 影响 | ORM 批量更新生成的 SQL 容易触发该错误 |