CODEWITHSUNDEEP
mysql
Arindom Chanda
Arindom Chanda

Reputation: 11

Output of select query in where clause not working

Update table set Style='new' where id=(SELECT MAX(id) From table); This is my query. But not working in mysql. Error shows: You can't specify target table 'table' for update in FROM clause Please help.

Upvotes: 0

Views: 65

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

You can store the result of the subquery in a session variable, like so:

SELECT MAX(id) INTO @maxID 
FROM table
;

UPDATE table 
SET Style='new' 
WHERE id=@maxID
;

Note that the operation is no longer "atomic"; depending on the activity going on, it is potentially possible for that MAX(id) to change between the SELECT and the UPDATE.

Another possibility is: 

UPDATE table
SET Style = 'new'
ORDER BY id DESC
LIMIT 1
;

But I tend to (for no particular reason) be averse to such queries.

Upvotes: 1

Related Questions