[RESOLVED] SQL - order an upate?

[shawjames]

I am adding a new field to my customer database, which is the date for when the last customer order was placed. I have setup so now going forward, it will update when the customer places a new order... but I want to update based on past orders.

UPDATE customers, orders SET customers.lastOrder = orders.orderDate WHERE customers.cID = orders.cID

This seems to set the LastOrder date to the first order found. I want to reverse order the orders table so that it sets the lastOrder date to the last order (which would be the first found in reverse order)

So, I tried adding on the end of this query: ORDER BY to_days(orders.orderDate) DESC

... but that caused an error.

I can do it with a simple PHP loop, but it might take awhile. Any slick ways to do this with just a query?

[Drakla]

Reckon not...

http://dev.mysql.com/doc/refman/5.0/en/update.html

"For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used. "

[leatherback]

you could look at nested query (Dunno whether it works!):

update TABLE set colum = value where ID = 'select id where ... order by to_days(orderDate) DESC'