MySQL Update Error 1093

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 spec开发者_如何转开发ify 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';

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.