CODEWITHSUNDEEP
mysqlsql
Dylan
Dylan

Reputation: 955

How to update some row where ID = SELECT (...) - SQL

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(

Upvotes: 2

Views: 232

Answers (4)

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

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

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

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

Upvotes: 0

Manashvi Birla
Manashvi Birla

Reputation: 2843

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

Upvotes: -1

Māris Kiseļovs
Māris Kiseļovs

Reputation: 17295

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

Upvotes: 2

Related Questions