CODEWITHSUNDEEP
phpmysql
nico55555
nico55555

Reputation: 65

Incorrect usage of UPDATE and ORDER BY

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());  
}

Upvotes: 2

Views: 2034

Answers (3)

jayellos
jayellos

Reputation: 691

Just remove your ORDER BY in your UPDATE statement, then put it in your SELECT statement.

sample:

$query = "SELECT ........ ORDER BY ..."
$result = mysql_query($query);

while(....){.... }

UPDATE statement wont accept ORDER BY clause.

Upvotes: 1

GregM
GregM

Reputation: 2654

Why do you need an order by in an update. I think you could just remove it and you update will update everything that respect your where statement.

EDIT: And maybe you could call a stored proc to simplify your code

Upvotes: 0

Tim
Tim

Reputation: 14446

You could use a SELECT call to loop through the rows, and include your WHERE and ORDER BY statements there, and then within your while($row = mysql_fetch_assoc($query)){ loop you'd have your UPDATE table SET key = 'value' WHERE id = '{$row['id']}' statement.

Sure, this would require executing mysql_query() a lot, but it'll still run pretty fast, just not at the same speed a single query would.

Upvotes: 0

Related Questions