SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Member
    Join Date
    Feb 2002
    Location
    Prince George, BC
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with update, set, order by, limit, in same query. I get "Syntax error"...

    Hi folks...

    I'm a total newbie to forum, so please excuse any errors in ethics or such...but this little problem's got me puzzled!

    The MySQL manual states:
    ...."UPDATE updates columns in existing table rows with new values. The SET clause indicates which columns to modify and the values they should be given. The WHERE clause, if given, specifies which rows should be updated. Otherwise all rows are updated. If the ORDER BY clause is specified, the rows will be updated in the order that is specified."......

    Also:
    ...."In MySQL Version 3.23, you can use LIMIT # to ensure that only a given number of rows are changed."....

    Then why pray-tell does this PHP query:
    "update table_name set confirmed='Y' where category='value' and confirmed='N' order by date LIMIT 3;"

    Produce this Notice:
    "Query Failed: You have an error in your SQL syntax near 'order by date LIMIT 3 ' at line 1"....

    I'm not completely new to php/mysql as I have designed a basic database site (thanks in great part to sitepoint's articles)... but man I could use a couple of hints to try out...





  2. #2
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    because you have your ; in the wrong place!

    Try this:

    PHP Code:
    "update table_name set confirmed='Y' where category='value' and confirmed='N' order by date LIMIT 3"
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  3. #3
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whilst you can use LIMIT in an update (http://www.mysql.com/doc/U/P/UPDATE.html) it doesn't say you can use order by. Try removing that.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  4. #4
    SitePoint Member
    Join Date
    Feb 2002
    Location
    Prince George, BC
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With due respect...In my original post I quoted exactly what the manual says (your link points to it).

    If you notice the last sentence reads:
    "...are updated. If the ORDER BY clause is specified, the rows will be updated in the order that is specified."...
    Seems to me that you can use "ORDER BY"...

    BTW the semicolon is not the problem cause I put it inside the quotes just for this post...I thought it would be clearer.

    I simply want to UPDATE a column entry in the oldest few rows in a table..(they all have the date entered)...

    There must be a way to do it in mysql..

  5. #5
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, you're right, I dont think I was really with it yesterday (I just quickly scanned the very top bit and missed the ORDER BY thing... )

    Well I had an idea, but I have tried it, and it refuses to work. In phpmyadmin, everytime, I get an error with ORDER BY. Limit is fine.

    I searched on the function ORDER BY and it doesn't bring up anything to do with UPDATE. You can use it with ALTER and SELECT.

    I have no idea, except that it might be an error in the manual.

    Sorry.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  6. #6
    Action! filmfoto's Avatar
    Join Date
    Dec 2001
    Location
    Sweden
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please post the syntax error you've received. It will help us help you.

    <edit> I missed the error in your post, sorry.

    I've read somewhere that you should not mix case in a MySQL query. This is a long shot, but try this instead:
    PHP Code:
    mysql_query("update table_name set confirmed='Y' where category='value' and confirmed='N' order by date limit 3"); 
    </edit>





    Cheers.
    Last edited by filmfoto; Feb 10, 2002 at 15:10.

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    filmfoto, i've never seen the case of SQL words make any difference.

    kal, the problem is: you can't use ORDER BY for UPDATEs! the manual says that b/c you can in MySQL 4.0.x. here, from the change-log for 4.0.0:

    Added ORDER BY syntax to UPDATE and DELETE.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  8. #8
    SitePoint Member
    Join Date
    Feb 2002
    Location
    Prince George, BC
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks much for your interest/attempts so far...but still status quo on problemo

    filmfoto... I tried but case makes no diff.

    DR_LaRRY... pardon me but I don't quite get it...since the changelog (your link) says:
    "Added ORDER BY syntax to UPDATE and DELETE."...then does that not mean that we now can use "ORDER BY"..since they "added" it..????

    Does anyone perhaps have a different solution to accomplish my goal, which is:
    ...to change a column entry in the oldest few submissions/or rows in a table...

    Any further thoughts fellow "Head scratchers"..?

  9. #9
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What DR_L_P means is that the ORDER BY syntax will only work on UPDATE with MySQL 4.0. Check your version, but I am guessing yuo are on 3.23 or thereabouts.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  10. #10
    SitePoint Member
    Join Date
    Feb 2002
    Location
    Prince George, BC
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Grasshopper is humbled and chagrined by the collective wisdom of you gurus... I checked and lo and behold... I have Version 3.23

    ...still.. the question remains... "Does anyone perhaps have a different solution to accomplish my goal, which is:"
    ...to change a column entry in the oldest few submissions/or rows in a mysql table...

  11. #11
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by kal

    ...still.. the question remains... "Does anyone perhaps have a different solution to accomplish my goal, which is:"
    ...to change a column entry in the oldest few submissions/or rows in a mysql table...
    Well, since mySQL still doesn't handle subselects well, I would suggest doing a select of the table ordered by the date descending. Loop through those records and update them one at a time.

    Hope that gives you the direction you need.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  12. #12
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hmm.

    Well I was trying to work along differnt lines, but since you can't do subselects and things like MIN() are for use with group by only,something like this should do the job (haven't tested it):

    PHP Code:
    $getentries=mysql_query("SELECT * from table_name WHERE confirmed='N' and category='value' ORDER by DATE desc LIMIT 3");

    while(
    $row=mysql_fetch_array($getentries)) {

    $update=mysql_query("UPDATE table_name SET confirmed='Y'");


    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  13. #13
    SitePoint Member
    Join Date
    Feb 2002
    Location
    Prince George, BC
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well...I tried the queries with while looping and the sucker changed the value in "ALL" the entries (not limited to 3)..

    In retrospect ...Is not the "while" function usable only for "using" the selected info?

    ie: You can not "change" or "update" the selected info, as it is in the computer temporary memory only at this point, and will simply be erased (not returned)...when you are finished looping and using the info???

    In any case I'm still in need of help...or can it not be done in mysql...?

  14. #14
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Like I said I ran that off the top of my head, use this:

    PHP Code:
    $get=mysql_query("SELECT * from test WHERE confirmed='N' and category='category' LIMIT 3");

    while(
    $row=mysql_fetch_array($get)) {

    $id=$row["id"];
    $update=mysql_query("UPDATE test SET confirmed='Y' where id='$id'");


    Last edited by weirdbeardmt; Feb 13, 2002 at 06:39.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  15. #15
    SitePoint Member
    Join Date
    Feb 2002
    Location
    Prince George, BC
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    B-I-N-G-O !!!!

    ...You have steered grasshopper in the right direction...the variable "$id" in the while() function did the trick !

    I was wrong...It "IS" possible to change/update the info selected and looped with a while() function..and then return the updated info to the table..

    ..With utmost humbleness a small point..to update the "OLDEST" few entries in a table you don't put "DESC" into the query (just leave it blank) since "ASCENDING is actually the order you need.

    For the sake of any other lowly grasshoppers (such as myself)...here is what I have learned with the gentle guidance of Gurus DaveMaxwell, DR_LaRRY_PEpPeR and the venerable weirdbeardmt:

    (1) Pre 4.0 versions of MySQL do not permit the use of ORDER BY with the UPDATE query.

    (2) This can be achived by simply using the desired "ORDER BY" (and "LIMIT" if needed) in a select query, then UPDATING the resulting entries as desired within a looping "while()" function...as shown above in weirdbeardmt's last post.

    Grasshoppers antennae are twisted in awe and gratitude to you mentor/gurus...Muchos thanks amigos!

  16. #16
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by kal

    ..With utmost humbleness a small point..to update the "OLDEST" few entries in a table you don't put "DESC" into the query (just leave it blank) since "ASCENDING is actually the order you need.
    Oops, well spotted. I am used to putting DESC because all my stuff seems to list from most recent date at the top.

    I'm pleased it's working
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  17. #17
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, since you're updating confirmed to a single value (Y), i would do it like this with 2 queries instead of a loop. doesn't matter much with only 3 entries, but if there were more it's much better to have fewer queries.

    BTW, weird, you put quotes around a number ($id).

    PHP Code:
    $r mysql_query("SELECT id FROM table WHERE confirmed='N' AND category='category' ORDER BY date LIMIT 3");

    while (
    $row mysql_fetch_row($r)) { $ids[] = $row[0]; }

    mysql_query("UPDATE table SET confirmed='Y' WHERE id IN(0" implode(','$ids) . ")"); 
    Last edited by DR_LaRRY_PEpPeR; Feb 15, 2002 at 02:19.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  18. #18
    SitePoint Member
    Join Date
    Feb 2002
    Location
    Prince George, BC
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks "Doc"...for your further input...but I'm afraid the "IN() and implode()..(functions..??) are greek to me

    ..I will certainly learn about them when I might need them...but for now.."If it ain't broke"..you know

    BTW..the quotes around ["$id"]..work ok for me..but you probably have a point.. I'll check it out.

    Man.. I just love this programming stuff, but holy cow-chips... "it sure ain't no exact science"!

  19. #19
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry i didn't make myself clear. i didn't mean the quotes at $id=$row["id"]; i meant in the MySQL query: ... WHERE id='$id'

    IN() is a MySQL function that makes

    col IN(1, 2, 3)

    the same as

    col=1 OR col=2 OR col=3

    either way will update the rows with the corresponding ids in 1 query.

    implode() is the PHP function that makes the comma seperated list of ids for IN(). it puts the string (,) in between each array element ($ids).

    hope that helps some more. i understand "if it ain't broke..."
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  20. #20
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by DR_LaRRY_PEpPeR

    BTW, weird, you put quotes around a number ($id).
    <GRABS RULER AND SLAPS WRISTS />



    While we're at it, Matt, can you explain the diff in results between mysql_fetch_array and mysql_fetch_row...
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  21. #21
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the manual entry for mysql_fetch_array() explains it pretty well. basically, fetch_row() = numeric array indexes only. fetch_array() = numeric and associative indexes.


Bookmarks

Posting Permissions

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