SQLite - UPDATE with LIMIT 1 gives syntax error

[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 September 11, 2008 by wysocki

[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 September 11, 2008 by PsaltyDS

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

[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

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