mysql - 如何更新 ID = SELECT (...) 的某些行 - SQL

Question

我有一个表 - “日志”。

+-------+--------+-----------+---------------+
| logId | spName | startTime | executionTime |
+-------+--------+-----------+---------------+
|  1    | mail   | 11:54:49  |    300        |
|  2    | calc   | 13:12:11  |    500        |
| ....  |        |           |               |
|  100  | log    | 15:30:30  |    400        |
+-------+--------+-----------+---------------+

我想像这样更新它:

UPDATE logs 
SET executionTime = 600
WHERE logId = ( SELECT max(logId) FROM logs WHERE spName = 'calc' )

但是我收到一个错误:

Error Code: 1093. You can't specify target table 'logs' for update in FROM clause

我不知道怎么解决(

Answer 1

您可以通过使用 ORDER By 和 LIMIT 对单个查询执行此操作

UPDATE logs 
SET executionTime = 600
WHERE spName = 'calc'
ORDER By logId DESC
LIMIT 1

这将只更新具有最大 logId 的一行