title: Incorrect usage of UPDATE and ORDER BY
body: <p>I have written some code to update certain rows of a table with a decreasing sequence of numbers. To select the correct rows I have to JOIN two tables. The last row in the table needs to have a value of 0, the second last -1 and so on. To achieve this I use ORDER BY DESC. Unfortunately my code brings up the following error:</p>
  
  <p>Incorrect usage of UPDATE and ORDER BY</p>
  
  <p>My reading suggests that I can't use UPDATE, JOIN and ORDER BY together. I've read that maybe subqueries might help? I don't really have any idea how to change my code to do this. Perhaps someone could post a modified version that will work?</p>
  
  <pre><code>while($row = mysql_fetch_array( $result )) {
  
  $products_id = $row['products_id'];
  $products_stock_attributes = $row['products_stock_attributes']; 
  
  mysql_query("SET @i = 0");
  
  $result2 = mysql_query("UPDATE orders_products op, orders ord
  
                     SET op.stock_when_purchased = (@i:=(@i - op.products_quantity)) 
                     WHERE op.orders_id = ord.orders_id 
  AND op.products_id = '$products_id' 
  AND op.products_stock_attributes = '$products_stock_attributes' 
  AND op.stock_when_purchased &lt; 0 
  AND ord.orders_status = 2
  
  ORDER BY orders_products_id DESC") 
  or die(mysql_error());  
  }
  </code></pre>