MySQL中用于根据另一个字段的最大值更新字段的嵌套select语句会产生错误

Question

I want to execute the following SQL statement in PhP. I have simplified it below. I would like to know the MySQL modifications to make it work. I tried a few like giving aliases to the tables but it did not work:

     update T1 set col1 = 500 
         where  col2 = 12345 and 
                col3 = (select max(col3) from T1 where  col2 = 12345) 

I want to update a record which has the max value for one column. There is only one table involved in the entire query. I am using PDO, if that is relevant.

The error given is:

#1093 - You can't specify target table 'T1' for update in FROM clause 

Answer 1

Use a join instead:

 update T1 join
        (select max(col3) as maxcol3
         from T1 t11
         where col2 = 12345
        ) tmax
        on T1.col3 = tmax.maxcol3 and
           T1.col2 = 12345
     set T1.col1 = 500;