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;