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'