现在,我正在尝试从表中的列中获取最大值,然后将其递增(+1),然后将另一条记录同一列更新为该数字。我正在用PHP编写脚本,变量$SQL被发送到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;
错误是:无法在 FROM 子句中指定要更新的目标表"路由器"我们应该如何做到这一点,而不是用 php 编写单独的查询
尝试以下查询:
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` = ?