带有选择数据的MySQL更新表

Question

Now, I'm trying to get the highest value from a column in a table then increment it(+1), then update another records same column to that number. I'm scripting in PHP, variable $SQL is sent to MySQL.

    $SQL="
        UPDATE `router`
        SET `Line Order`= (SELECT 1 + IFNULL(MAX(`Line Order`), 0) FROM `router`)
        WHERE `RN`=?
    ";
    $stmt = $GLOBALS['mySQLConnection']->prepare($SQL);
    echo $GLOBALS['mySQLConnection']->error;

The error is: You can't specify target table 'router' for update in FROM clause How are we suppose to do this, without writing separate queries in php

Answer 1

Try this query:

UPDATE router AS r1
INNER JOIN (
    SELECT 1 + IFNULL(MAX(`Line Order`), 0) AS new FROM `router`) AS r2
SET `Line Order` = r2.new
WHERE `RN` = ?