CODEWITHSUNDEEP
mysqlsqldatabase
Harshana
Harshana

Reputation: 7647

inner join with max not working

I have the following table and i am try to increase the ref column value with 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'

But it always set to 0 instead if 2.

Thanks.

Upvotes: 1

Views: 146

Answers (3)

Andomar
Andomar

Reputation: 238086

It looks like you're setting the integer column ref to a string containing a SQL statement:

set ref = '(SELECT (MAX(ref) + 1) AS max_ref FROM table1)'

When MySQL tries to convert that string to an integer, it ends up with 0.

The right way is to omit the '' quotes, which leaves you at a new problem: MySQL doesn't allow you to update and select from the same table. You'll find a solution for that in both other answers.

Upvotes: 1

Grzegorz Szpetkowski
Grzegorz Szpetkowski

Reputation: 37924

According to MySQL doc:

Currently, you cannot update a table and select from the same table in a subquery.

However it's easy to bypass:

UPDATE table1
SET ref = (
    SELECT m.max_ref FROM (
        SELECT (max(ref) + 1) AS max_ref FROM table1) m)
WHERE name= 'B'

Upvotes: 1

Shef
Shef

Reputation: 45589

Try:

UPDATE `table1`
SET `ref` = (
        SELECT `cur_ref` FROM(
                SELECT MAX(`ref`)+1 `cur_ref` FROM `table1`
        ) `calc_table`
) WHERE `name` = 'B'

Upvotes: 0

Related Questions