CODEWITHSUNDEEP
phpmysql
serv92
serv92

Reputation: 121

MySQL update table with select data

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

Upvotes: 0

Views: 102

Answers (1)

RNK
RNK

Reputation: 5792

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` = ?

Upvotes: 1

Related Questions