Well, Jeff you are right! I've just taken the
wrong line from .mysql_history file. It was one
of my thousands of tries to make the ORDER BY work
with UPDATE. What I really try to do is ORDER BY
the 'received' column, which is a timestamp.
The point is if I have plenty of alarm messages
which are not parsed I would like to take and parse
the one that has come first in time. And because
this is done from concurent clients it is crucial
to parse it with just one update query and
not with ordered select and then update.
So here is the real query:
mysql> UPDATE alarms SET is_parsed=1 WHERE is_parsed=0
ORDER BY received LIMIT 1;
ERROR 1064: You have an error in your SQL syntax near
'ORDER BY received LIMIT 1' at line 1
What I found is that UPDATE .... SET .... WHERE .... ORDER BY ....
never works in MySQL no matter what your table looks like.
And according to MySQL manual the UPDATE synthax is:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1, [col_name2=expr2, ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT #]
Is this a bug in MySQL or the manual is not correct?!?
Has anyone managed to use ORDER BY with an UPDATE statement?
Thank you in advance!
George
------------Jeff wrote:
>Why do you need the ORDER BY clause? Can you take it out?
>For example:
>
>UPDATE alarms SET is_parsed=1 WHERE is_parsed=0 LIMIT 1;
>
>I just don't think the ORDER BY clause could serve any
>useful purpose there
>and appears to actually cause problems.
>
>Good luck,
>Jeff
>>-----Original Message-----
>>Hi!
>>
>>The MySQL manual states that you can use ORDER BY in
>>combination with the UPDATE statement.
>>So I tried to use it, but it didn't work.
>>Here is the sql query that's causing the trouble:
>>
>>mysql> UPDATE alarms SET is_parsed=1 WHERE is_parsed=0
>> ORDER BY is_parsed LIMIT 1;
>>
>>ERROR 1064: You have an error in your SQL syntax near
>> 'order by is_parsed limit 1' at line 1
>>
>>The same error happens when I don't use LIMIT:
>>
>>mysql> UPDATE alarms SET is_parsed=1 WHERE is_parsed=0
>> ORDER BY is_parsed;
>>
>>ERROR 1064: You have an error in your SQL syntax near
>> 'order by is_parsed' at line 1
>>
>>Here is the table's structure:
>>
>>CREATE TABLE alarms (
>> serial int(10) unsigned NOT NULL auto_increment,
>> sys_num tinyint(2) unsigned NOT NULL default '1',
>> rtu_num smallint(4) unsigned NOT NULL default '0',
>> status char(2) NOT NULL default 'XX',
>> received datetime default NULL,
>> device tinyint(2) unsigned NOT NULL default '0',
>> is_parsed tinyint(1) unsigned NOT NULL default '0',
>> PRIMARY KEY (serial),
>> KEY is_parsed (is_parsed),
>> KEY rtu_num (rtu_num)
>>) TYPE=MyISAM;
----
Безплатна пощенска служба http://mail.Rambler.bg/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
