MySQL 更新错误 1093
这适用于以 doc_id 为主键的表：
select count(*)+1 from doctor where
exp > (select exp from doctor where doc_id='001');
+------------+
| count(*)+1 |
+------------+
| 2 |
+------------+
但是当我使用相同的选择查询来设置表中的字段时，它会报告以下错误：
update doctor set rank=
( select count(*)+1 from doctor where
exp > (select exp from doctor where doc_id='001')
) where doc_id='001';
ERROR 1093 (HY000): You can't specify target table 'doctor' for update
in FROM clause
我无法理解哪个目标它正在谈论的表参考。有人可以解释一下吗？
This works in a table where doc_id is the primary key:
select count(*)+1 from doctor where
exp > (select exp from doctor where doc_id='001');
+------------+
| count(*)+1 |
+------------+
| 2 |
+------------+
But when I'm using the same select query to set a field in the table, it reports the following error:
update doctor set rank=
( select count(*)+1 from doctor where
exp > (select exp from doctor where doc_id='001')
) where doc_id='001';
ERROR 1093 (HY000): You can't specify target table 'doctor' for update
in FROM clause
I can't understand which target table reference it is talking of. Can someone explain?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
此限制记录在 MySQL 手册中：
作为解决方法，您可以将子查询包装在另一个子查询中并避免该错误：
This restriction is documented in the MySQL manual:
As a workaround, you can wrap the sub-query in another sub-query and avoid that error:
您不能在 from 子句的子查询中使用要更新的表。尝试连接或双子查询:)
You can't use a table you're updating in a subquery in the from clause. Try a join, or a double subquery :)
我认为这可能是因为您在同一张表上读取和写入。这可能是阻止写入的预防性方法，因为您的更新可能会影响正在读取的数据。
您可能需要将子查询分离为使用中间虚拟表。
I think it might be because you're reading and writing from the same table. This could be a preventive way of blocking writing because your update could affect the data being read.
You might need to separate the subquery into using an intermediate virtual table.