MySQL Error: You can't specify target table 'users_words' for update in FROM clause

Question

Please have a look at my MySQL clause.

UPDATE `users_words` SET `priority` = (SELECT MAX(`priority`)+1 FROM `users_words`) where `userid` = 89 AND `wordid`="agree"

Here I am going to update the priority field by adding 1 to the existing maximum priority field value. But I am getting the error You can't specify target table 'users_words' for update in FROM clause.

How can I fix this issue?

Answer 1

Just wrap your subquery in another SELECT :

UPDATE `users_words` 
SET `priority` = (SELECT x.* FROM (SELECT MAX(`priority`)+1 FROM `users_words`) x) 
WHERE `userid` = 89 AND `wordid`="agree"