Search code examples
mysqlsqlsql-updatemysql-error-1093

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 specify target table 'doctor' for update 
in FROM clause

I can't understand which target table reference it is talking of. Can someone explain?

Solution

  • 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';