这适用于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
我无法理解它正在讨论哪个目标表引用.谁能解释一下？
最佳答案
此限制记录在MySQL manual中：
原文链接：https://www.f2er.com/mysql/432981.htmlCurrently,you cannot update a table and select from the same table in a subquery.
作为一种变通方法,您可以将子查询包装在另一个子查询中并避免该错误：
update doctor set rank=
(select rank from ( select count(*)+1 as rank from doctor where
exp > (select exp from doctor where doc_id='001')
) as sub_query) where doc_id='001';