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?
This restriction is documented in the MySQL manual:
Currently, you cannot update a table and select from the same table in a subquery.
As a workaround, you can wrap the sub-query in another sub-query and avoid that error:
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';