在MySQL中，不能在FROM子句中指定更新的目标表 - You can't specify target table for update in FROM clause in MySQL

You can't specify target table for update in FROM clause in MySQL

我想通过选择其requisition_id来更改单个订单的status，其中status与特定关键字匹配。但是它一直显示这个错误"你不能在FROM子句中指定更新的目标表"

UPDATE order_info
SET status ='Approved' 
WHERE requisition_no IN (SELECT requisition_no FROM order_info WHERE status = 'not approved')

我能做些什么来修复它?

EDIT-我也包含了PHP代码

while ($row = mysqli_fetch_array($result)){
echo "<tr>";
    echo "<td>" . $row['requisition_no'] . "</td>";
    echo "<td>" . $row['desig'] . "</td>";
    echo "<td>" . $row['deptt'] . "</td>";
    echo "<td>" . $row['type'] . "</td>";
    echo "<td>" . $row['make'] . "</td>";
    echo "<td>" . $row['quantity'] . "</td>";
    echo '<form method="post" action="status.php">'; /*Redirects to status.php to change the status (using diff. page because i'm a hopeless kid, who doesnt want to learn new ways of simplifying things)*/
    echo "<td>" . $row['status'] . "<br/>";
    if($row['status']=='Approved' || $row['status']=='Under Procurement'){//does nothing
    }
            /*Here, using a submit button*/
    else { echo '<input type="submit" name="status" value="Approve"></td>';
    $_SESSION['rno'] = $row['requisition_no'];}
    echo '</form>';

您可能有一些语法错误导致此错误。您在status之后缺少等号=，并在末尾缺少右括号):

UPDATE order_info o
SET o.status = 'Approved' 
WHERE o.requisition_no IN (SELECT x.requisition_no FROM (select * from order_info) x WHERE x.status = 'not approved')

同时，我认为你的查询可以这样重写:

UPDATE order_info
SET status = 'Approved' 
WHERE status = 'not approved'