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

[kal]

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...

[weirdbeardmt]

because you have your ; in the wrong place!

Try this:

"update table_name set confirmed='Y' where category='value' and confirmed='N' order by date LIMIT 3"; 

[weirdbeardmt]

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.

[kal]

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..

[weirdbeardmt]

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.

[filmfoto]

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:

mysql_query("update table_name set confirmed='Y' where category='value' and confirmed='N' order by date limit 3"); 

</edit>





Cheers.

[DR_LaRRY_PEpPeR]

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.

[kal]

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"..?

[weirdbeardmt]

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.

[kal]

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...

[DaveMaxwell]

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.

[weirdbeardmt]

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):

$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'");

} 

[kal]

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...?

[weirdbeardmt]

Like I said I ran that off the top of my head, use this:

$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'");

} 

[kal]

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!

[weirdbeardmt]

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

[DR_LaRRY_PEpPeR]

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).

$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) . ")"); 

[kal]

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"!

[DR_LaRRY_PEpPeR]

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..."

[weirdbeardmt]

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...

[DR_LaRRY_PEpPeR]

the manual entry for mysql_fetch_array() explains it pretty well. basically, fetch_row() = numeric array indexes only. fetch_array() = numeric and associative indexes.