Android Question Help with a query

[Alex_197]

Hi all.

I don't understand what is wrong with this query

UPDATE tblClientGivenMedicine_Temp
SET Notes = '555'
where JobDate='12/20/2025' and DrugID=11197
ORDER by Created DESC
LIMIT 1;

I'm getting error

Execution finished with errors.
Result: near "ORDER": syntax error
At line 3:
UPDATE tblClientGivenMedicine_Temp
SET Notes = '555'
where JobDate='12/20/2025' and DrugID=11197
ORDER

according to this page https://www.sqlitetutorial.net/sqlite-update/ it's possible

Thanks

 

[ilan]

As answered by other members above, ORDER BY and LIMIT keywords are not supported in SQLite by default when doing an UPDATE.
The workaround is posted by @LucaMs but as my reply on post #15, the row to be updated would not be the last row.
If the table has a primary key as my example code above, we can use MAX(rowid) to get the last row.

SQL1.ExecNonQuery2("UPDATE tblClientGivenMedicine_Temp SET Notes = ? WHERE rowid = (SELECT MAX(rowid) FROM tblClientGivenMedicine_Temp WHERE JobDate = ? AND DrugID = ?)", Array As Object("555", "12/20/2025", 11197))

So instead of rowid = 4 is updated, rowid = 6 is the one getting updated.

yes i understand what you mean but the sqlite tutorial is misleading and also wrong:

they also give a button and tell you TRY IT and if you press on it you are redirected to a sqlite editor where the exact code is passed as in the tutorial but if you run it it fails:

SQLite Editor

Practice basic SQLite using the live SQLite editor online.

www.sqlitetutorial.net

maybe they forgot to update the tutorial?!

 

[teddybear]

UPDATE tblClientGivenMedicine_Temp
SET Notes = '555'
where JobDate='12/20/2025' and DrugID=11197
ORDER by Created DESC
LIMIT 1;

The sql has no problem if ORDER BY and LIMIT clauses for UPDATE are available.
It will update the 1st record which selected by conditions and orderby key.

select * from emp where nm="Alice" order by idx desc;

update emp set boss=168
where nm="Alice"
ORDER by idx desc
LIMIT 1;

 

[MrKim]

It is just a BAD idea to try and use an ORDER BY and LIMIT in an update query. If you are going to modify data you better know EXACTLY what you are modifying.
But if you must some DB engine specific version of LucaMs's post 13 will get you there with only one trip to the server.