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:

Incorrect usage of UPDATE and ORDER BY

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?

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 < 0 
AND ord.orders_status = 2

ORDER BY orders_products_id DESC") 
or die(mysql_error());  
}