Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

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.

like image 947
chris Avatar asked Sep 03 '11 10:09

chris


People also ask

How to use SELECT and UPDATE together in MySQL?

Just add them together: UPDATE LOG SET TIME_EXIT = '2013/02/22' WHERE ID = ( SELECT ID FROM employee ORDER BY ID DESC LIMIT );

How to UPDATE a column in a table in MySQL?

MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.

Which clause in MySQL indicates which columns to modify and the values they should be given while being used in UPDATE statement?

The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value.


1 Answers

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.

like image 74
Martin Smith Avatar answered Nov 14 '22 21:11

Martin Smith