MySQL update error 1093

Question

This works in the table, where doc_idis the primary key:

select count(*)+1 from doctor where 
exp > (select exp from doctor where doc_id='001');

+------------+
| count(*)+1 |
+------------+
|          2 |
+------------+

But when I use the same select query to set the 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 what target link table she is talking about. Can someone explain?

Answer 1

This limitation is described in the MySQL User Guide :

Currently, you cannot update the table and select from the same table in the subquery.

As a workaround, you can wrap the subquery in another subquery and avoid this 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';

Answer 2

, , from. :)

Answer 3

I think it could be because you are reading and writing from the same table. This can be a proactive way to block writing, as your update may affect reading data.

You may need to split the subquery into an intermediate virtual table.