Jump to content
Sign in to follow this  
wysocki

SQLite - UPDATE with LIMIT 1 gives syntax error

Recommended Posts

wysocki

I wish there was some good place to get more information on using SQLite in Autoit. The SQLite help file is really weak and the website is not much better. As far as I can figure, this should work, but it doesn't:

_SQLite_Exec(-1, 'UPDATE mytable SET amount=999.99 WHERE code=0 LIMIT 1;')

The MYsql help at http://dev.mysql.com/doc/refman/5.0/en/update.html says this is ok, and the SQLite help doesn't show it as an excluded feature.

I thought I saw some reserved word that represents "the row last selected" but I can't find it anymore. Maybe this could be used after a SELECT...LIMIT operation?

$sQuery = "SELECT code FROM mytable WHERE code=0 LIMIT 1"; is limit 1 doing any good here?
_SQLite_QuerySingleRow(-1, $sQuery, $iRows)
If $iRows[0] <> "" Then 
 ;UPDATE but now I still have to include a WHERE clause for the record I'll want to update
else 
 ;INSERT...
endif

I just want to update ONE of the matching records. How should this be done?

Edited by wysocki

Share this post


Link to post
Share on other sites
PsaltyDS

I wish there was some good place to get more information on using SQLite in Autoit. The SQLite help file is really weak and the website is not much better. As far as I can figure, this should work, but it doesn't:

_SQLite_Exec(-1, 'UPDATE mytable SET amount=999.99 WHERE code=0 LIMIT 1;')

The MYsql help at http://dev.mysql.com/doc/refman/5.0/en/update.html says this is ok, and the SQLite help doesn't show it as an excluded feature.

I thought I saw some reserved word that represents "the row last selected" but I can't find it anymore. Maybe this could be used after a SELECT...LIMIT operation?

$sQuery = "SELECT code FROM mytable WHERE code=0 LIMIT 1"; is limit 1 doing any good here?
_SQLite_QuerySingleRow(-1, $sQuery, $iRows)
If $iRows[0] <> "" Then 
;UPDATE but now I still have to include a WHERE clause for the record I'll want to update
else 
;INSERT...
endif

I just want to update ONE of the matching records. How should this be done?

I'm NOT an SQLite expert, but I would have assumed that operator was not available unless listed here: SQL As Understood By SQLite: UPDATE

Did you by any chance try _SQLite_QuerySingleRow()? Oops, read through it too fast, that's exactly what you were doing.

:)

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
wysocki

The docs for UPDATE that you point to just say "a WHERE clause may be used..." which is like most other SQLs. There's a page at http://www.sqlite.org/omitted.html which doesn't mention any omission of LIMIT from SQLite.

As my example shows, I have used _SQLite_QuerySingleRow() as a workaround. However, this seems very inefficient, since it first scans the table for the matching record named in the WHERE clause, then the following UPDATE statement does the same WHERE search to locate the record to be updated. Furthermore, the UPDATE WHERE will process multiple rows that match the WHERE instead of just one as I want.

Once I've done the _SQLite_QuerySingleRow(), is there any way of updating just the ONE row that was found by it without having to rescan the entire table again?

Share this post


Link to post
Share on other sites
JSThePatriot

Do you have a primary key set for the table? That would be easy to set in the where clause of the update pulling that value from the select statement.

Hope it helps some,

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites
PsaltyDS

The docs for UPDATE that you point to just say "a WHERE clause may be used..." which is like most other SQLs. There's a page at http://www.sqlite.org/omitted.html which doesn't mention any omission of LIMIT from SQLite.

As my example shows, I have used _SQLite_QuerySingleRow() as a workaround. However, this seems very inefficient, since it first scans the table for the matching record named in the WHERE clause, then the following UPDATE statement does the same WHERE search to locate the record to be updated. Furthermore, the UPDATE WHERE will process multiple rows that match the WHERE instead of just one as I want.

Once I've done the _SQLite_QuerySingleRow(), is there any way of updating just the ONE row that was found by it without having to rescan the entire table again?

No, I was totally off base. You were using _SQLite_QuerySingleRow(), and more to the point, while UPDATE does not show the LIMIT operator in SQLite, SELECT does!

The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradition is intentional - it maximizes compatibility with legacy SQL database systems.

So, as proposed by JSThePatriot, I would just pull both the code and ROWID columns then use the ROWID in the WHERE clause to specify which row to UPDATE.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.