I've the next table and i'm attempt to boost the ref column value using the following query.
orderid name age address ref 1 A 22 a1 1 2 B 21 b1 0 update table1 set ref = '(SELECT (MAX(ref) + 1) AS max_ref FROM table1)' where name= 'B'
However it always set to rather if 2.
Based on MySQL doc:
Presently, you can't update a table and choose in the same table inside a subquery.
However it's not hard to bypass:
UPDATE table1 SET ref = ( SELECT m.max_ref FROM ( SELECT (max(ref) + 1) AS max_ref FROM table1) m) WHERE name= 'B'
It appears like you are setting the integer column
ref to some string that contains a SQL statement:
set ref = '(SELECT (MAX(ref) + 1) AS max_ref FROM table1)'
When MySQL attempts to convert that string for an integer, it eventually ends up with .
The proper way would be to omit the
'' quotes, which leaves you in a new problem: MySQL does not permit you to update and choose in the same table. You'll take action for your both in other solutions.
UPDATE `table1` SET `ref` = ( SELECT `cur_ref` FROM( SELECT MAX(`ref`)+1 `cur_ref` FROM `table1` ) `calc_table` ) WHERE `name` = 'B'