2

I have a table - "logs".

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

I want to update it like:

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

But I receive an error:

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

I don't know how to fix it(

Dylan
  • 935
  • 1
  • 11
  • 24

4 Answers4

2

You can do this with single query by using ORDER By and LIMIT

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

This will update only one row with largest logId

Māris Kiseļovs
  • 16,957
  • 5
  • 41
  • 48
1

try this:

UPDATE logs a,
(SELECT SELECT max(logId) max_logs FROM logs WHERE spName = 'calc') AS b
SET executionTime = 600
WHERE a.logId = b.max_logs
Hotdin Gurning
  • 1,821
  • 3
  • 15
  • 24
0

You can use join update for this

update logs l1
join (
 select max(logId) from logs WHERE spName = 'calc'
)l2 on l1.logId = l2.logId
set l1.executionTime = 600
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
-1
UPDATE logs 
SET executionTime = 600
WHERE logId = ( SELECT logId FROM logs WHERE spName = 'calc' )
Manashvi Birla
  • 2,837
  • 3
  • 14
  • 28
  • This will return error. See http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Māris Kiseļovs Jun 03 '15 at 09:53