Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5

Thread: Update Order By

  1. #1
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts

    Update Order By

    I cannot add an ORDER BY clause to an update. even though the manual says I can.
    Code:
    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
    Code:
    #1064 - You have an error in your SQL syntax near 'ORDER BY fldBookingID
    LIMIT 1' at line 4
    Version is 3.23.58

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #3
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    bah. Its in the 3.23 manual though - any way around it?

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by NancyJ
    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.

  • #5
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    I've put in a 'where fldBookingID > 0' and it seems to be working - but only time will tell


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •