#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Colorado Springs, CO
    Posts
    22
    Rep Power
    0

    Update first X rows?


    I'm trying to do a query similar to:

    PHP Code:
    update uploads set `favorite` = true where `user_id` = '5' limit 5 order by `timestampasc 
    What I'm trying to do is set the variable to true for the first X number of rows. (It could be five, it could be five hundred.)

    It doesn't like "order by" being in there. I've seen something about "limit / order by" requiring subqueries if together, but I'd think there has to be a simpler solution to this. This seems like a relatively simple task that the developers would have included somehow. I'm no MySQL expert, so I figured I'd ask.

    Code:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by `timestamp` asc' at line 1
    Thanks!
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    Just like with a SELECT query, the ORDER BY has to come before the LIMIT.

    Comments on this post

    • airforcemook agrees
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1017
    Hi,

    The "ORDER BY" clause has to come before the "LIMIT" clause.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    LIMIT has to come after the ORDER BY clause

    anyone else?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,884
    Rep Power
    6355
    Not sure, but I think LIMIT has to be last, after the ORDER BY.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Colorado Springs, CO
    Posts
    22
    Rep Power
    0
    LOL thank you all.

    Such a simple fix. I knew it was possible.

    Comments on this post

    • ManiacDan agrees : For being a good sport.
    • r937 agrees : what dan said

IMN logo majestic logo threadwatch logo seochat tools logo