SQL query error (near 'LIMIT 1')

ipruthi

Hi,

I wrote this query:

query = "UPDATE tablename SET status='$status' WHERE ckey='$ckey' LIMIT 1";

and when I run it, it says this:

You have an error in your SQL syntax near 'LIMIT 1' at line 1

Thanx in advance,
Ilir.

kparker

LIMIT simply isn't a legal part of an UPDATE query. If your where-clause doesn't limit the udpate to exactly the row(s) you want to update, you're out of luck (maybe need to add a primary-key field after all, or be sure you retrieve the primary key and pass it around between scripts when setting up this query...)

Anon

Actually, LIMIT is legal in an update query.

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#UPDATE

The problem is most likely in either $ckey or $status...

Try using mysql_escape_string() on both $status and $ckey before placing them in the query... Here is the PHP manual page for this function:
http://www.php.net/manual/en/function.mysql-escape-string.php

kparker

Oh my, another MySql extension...

Anon

Yeah, I tried using the mysql_escape_string() function but it warned that there is an "undefined function". Yes, I made sure my syntax was correct and it was exactly as in PHP's manual. Is this a new function (in a newer PHP 4 compiler) or is my server's PHP disconfigured???

Thanx.

Oh yeah, what would happen if I had removed the "LIMIT 1" from the query??? I don't think it would make A BIG DIFFERENCE (would it?).

Thanks for your help. I appreciate it. I like the Open Source environment. 🙂

Ilir.

Anon

As far as I can tell it should not make a difference if ckey is a unique value (no two entries have the same value, even if it isn't set as a unique key).

Are there any single quotes in $status or $ckey? If so, make sure you change the single quote into a double quote (' changed to '', two seperate single quotes), OR comment out the single quote with a backslash (' changed to \').

ziong

is it related to the MySQL version?
Since Manual claim that LIMIT at UPDATE statment only work in Version 3.23.
I think you should check it out.