...

View Full Version : Update Order By



NancyJ
08-15-2006, 10:09 AM
I cannot add an ORDER BY clause to an update. even though the manual says I can.


UPDATE tblBookings
SET fldOwner = 'test', fldStatus = 'In Progress', fldRequestDate = NOW()
WHERE fldOwner = '' AND fldStatus = 'New' and fldBookingDate > '2006-08-01'
ORDER BY fldBookingID
LIMIT 1



#1064 - You have an error in your SQL syntax near 'ORDER BY fldBookingID
LIMIT 1' at line 4


Version is 3.23.58

guelphdad
08-15-2006, 11:45 AM
You basically can't do anything with version 3, so most likely it will allow you to use the order by in some version of mysql 4.

NancyJ
08-15-2006, 11:59 AM
bah. Its in the 3.23 manual though - any way around it?

guelphdad
08-15-2006, 02:20 PM
bah. Its in the 3.23 manual though - any way around it?

From the manual:
If an UPDATE statement includes an ORDER BY clause, the rows are updated in the order specified by the clause. ORDER BY can be used from MySQL 4.0.0.

Unfortunately at the very top it shows the ORDER BY clause so you would think it can be used, but you always have to read through the entire thing so you can find out exactly which version of mysql something can be used by.

Your way around this would be to run your update as a select instead and note the max(fldBookingID) that would satisfy the condition and then run the update against that row.

The caveat of course is that new data could be inserted in the table with a higher fldBookingID before you run the update.

NancyJ
08-15-2006, 03:56 PM
I've put in a 'where fldBookingID > 0' and it seems to be working - but only time will tell



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum