mySQL UPDATE value based on SELECT value of value +1 Incrementing column value

Question

The Query:

UPDATE nominees SET votes = ( SELECT votes
FROM nominees
WHERE ID =1 ) +1

The Error:

You can't specify target table 'nominees' for update in FROM

Not sure whats wrong there based on the error, this is the first time im tryin to incriment a column inline i guess you can call it. So I am obvioulsy doing something wrong but dont know how to fix it.

Answer 1

Your UPDATE query is missing any WHERE clause so even if MySQL had allowed it then the effect would be to find the votes value for the ID =1 row add 1 to it then update all rows in the table with the result.

I suspect that was not the desired behaviour. To increment the column value you just need to do

UPDATE nominees 
SET votes = votes +1
WHERE ID =1 

Just in case you do want the other behaviour you would need to do

UPDATE nominees
SET    votes = (select votes + 1
                FROM   (SELECT votes
                        FROM   nominees
                        WHERE  ID = 1) T)  

This wrapping into a derived table avoids the You can't specify target table 'nominees' for update in FROM error you were getting.