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

Question

I want to change the status of a single order by selecting its requisition_id where the status matches a specific keyword. But it keeps showing me this error "You can't specify target table for update in FROM clause"

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

What can I do to fix it?

EDIT- I've included the PHP code as well

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>';

You are at least missing an equals sign and the right parenthesis `)`. — Patrick Hofman, Jun 06 '14 at 06:49
I didn't reread my query before posting it, no syntax errors in my actual code though. Still getting the same error. — dmail93, Jun 06 '14 at 06:55
See this related question: http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause — CyberDude, Jun 06 '14 at 07:01

Patrick Hofman · Accepted Answer · 2014-06-06T07:03:22.763

You have a few syntax errors probably causing this error. You are missing an equals sign = after status and the right parenthesis ) at the end:

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')

Also, I think your query can be rewritten like this:

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

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

1 Answers1