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

 

[Jerryk]

SQLite Query Language: UPDATE

Read conditions for using ORDER BY adn LIMIT

 

[hatzisn]

Also it would worth it to make sure that at the end of each row there is a [space] character.

 

[joko0124]

It depends on your DB engine:
If SQL or SQLite, do not use ORDER and LIMIT when using UPDATE query
If mySQL, change your date format from '12/20/2025' to '2025-12-20'

Hope it will help.

 

[Alex_197]

It depends on your DB engine:
If SQL or SQLite, do not use ORDER and LIMIT when using UPDATE query
If mySQL, change your date format from '12/20/2025' to '2025-12-20'

Hope it will help.

It's a SQLite.

 

[Mark Stuart]

Posts #3 and #4 are the guide lines.
In my years of building SQL statements, I've never used ORDER BY or LIMIT in an update statement.
The WHERE clause should point specifically to the record or records you're wanting to update.
Post #3 - I've hit this issue many times. Now I use the $""$ syntax. It allows you to type the SQL statement out and put the statement on separate lines. This helps in the reading of what you've typed.

HTH
Mark Stuart

 

[b4x-de]

I guess (and more than guessing is not possible to me based on the information provided here) the update will might affect less than 1 row. This might conflict with the limit of 1.

 

[Mark Stuart]

Hi,
How many rows do you think the UPDATE will affect?
Updating SQL records requires the WHERE statement to be accurate or the UPDATE will update records you don't want updating.
Is an example good for you since you're not completely understanding what I'm suggesting?

Mark Stuart

 

[b4x-de]

Your first question is easy to answer: your update will always affect ALL rows that confirm to the condition you provide in the where, that is: JobDate='12/20/2025' and DrugID=11197

This can be 1, 5, 10, 100000 or even zero. Nobody knows, because it depends on the data in your table and it may change over time.

If you seek to update exactly 1 row, you should access it via the primary key of your table or at least of any other unique column you have in this table. Then you need no limit or order by. The primary key is your friend!

Your second question: yes please give more insight into what you try to achieve by updating a random number of rows but limit the execution by pure coincidence to the first record with the latest create date.

 

[aeric]

Instead of posting the SQL query, it is better you post the exact B4X code containing that query. Sometimes a small mistake is not obvious.

 

[Alex_197]

The problem is here https://www.sqlitetutorial.net/sqlite-update/

3) Update with ORDER BY and LIMIT clauses example​

Notice that you need to build SQLite with SQLITE_ENABLE_UPDATE_DELETE_LIMIT option in order to perform UPDATE statement with optional ORDER BY and LIMIT clauses.

and what is funny itself that if you want to try the example of the Update with ORDER BY and LIMIT on the SQLite tutorial website it shows that same error https://www.sqlitetutorial.net/tryit/query/sqlite-update/#7

 

[Mark Stuart]

I looked at the example in the link. That's a really bad example, really bad.
Is that from you in the link or was it part of the website?
That is the first time I've seen an ORDER BY in an UPDATE statement. Ignore it and go with many other examples on your web searches.

 

[LucaMs]

I think that type of query could be useful if you want to correct an error in a table.

That query doesn't work; alternative (SQLite):

I had to post the query as an image otherwise the forum wouldn't allow posting the message.
(I assume due to protections set by Erel)

 

[teddybear]

According to the docs, ORDER BY and LIMIT clauses for UPDATE are available if SQLite has been built with SQLITE_ENABLE_UPDATE_DELETE_LIMIT.

The option SQLITE_ENABLE_UPDATE_DELETE_LIMIT normally turned off.
To use the feature you have to enablle the option and rebuild the library.

See here

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

This option enables an optional ORDER BY and LIMIT clause on UPDATE and DELETE statements.

If this option is defined, then it must also be defined when using the Lemon parser generator tool to generate a parse.c file. Because of this, this option may only be used when the library is built from source, not from the amalgamation or from the collection of pre-packaged C files provided for non-Unix like platforms on the website.

 

[aeric]

Even though the UPDATE is success, the record may be updated incorrectly if there are more than 1 row having Created column with same value.
I assume you want to update the last inserted row but the update may executed for the first matching row.

rowid|JobDate|DrugID|Notes|Created
1|12/20/2025|11196|-|1238936628
2|12/20/2025|11196|-|1238936628
3|12/20/2025|11197|-|1238936629
4|12/20/2025|11197|555|1238936630
5|12/20/2025|11197|-|1238936630
6|12/20/2025|11197|-|1238936630

Instead or rowid = 6 is being updated, the actual updated is rowid 4.

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private SQL1 As SQL
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    #If B4J
    Dim FileDir As String = File.DirApp
    #Else
    Dim FileDir As String = File.DirInternal
    #End If
    If File.Exists(FileDir, "data.db") = False Then
        #If B4J
        SQL1.InitializeSQLite(FileDir, "data.db", True)
        #Else
        SQL1.Initialize(FileDir, "data.db", True)
        #End If      
        SQL1.AddNonQueryToBatch("CREATE TABLE tblClientGivenMedicine_Temp (rowid INTEGER PRIMARY KEY, JobDate TEXT, DrugID INTEGER, Notes TEXT, Created TEXT)", Null)
        SQL1.AddNonQueryToBatch("INSERT INTO tblClientGivenMedicine_Temp (JobDate, DrugID, Notes, Created) VALUES (?, ?, ?, ?)", Array("12/20/2025", 11196, "-", DateTime.Now))
        SQL1.AddNonQueryToBatch("INSERT INTO tblClientGivenMedicine_Temp (JobDate, DrugID, Notes, Created) VALUES (?, ?, ?, ?)", Array("12/20/2025", 11196, "-", DateTime.Now))
        SQL1.AddNonQueryToBatch("INSERT INTO tblClientGivenMedicine_Temp (JobDate, DrugID, Notes, Created) VALUES (?, ?, ?, ?)", Array("12/20/2025", 11197, "-", DateTime.Now + 1))
        SQL1.AddNonQueryToBatch("INSERT INTO tblClientGivenMedicine_Temp (JobDate, DrugID, Notes, Created) VALUES (?, ?, ?, ?)", Array("12/20/2025", 11197, "-", DateTime.Now + 2))
        SQL1.AddNonQueryToBatch("INSERT INTO tblClientGivenMedicine_Temp (JobDate, DrugID, Notes, Created) VALUES (?, ?, ?, ?)", Array("12/20/2025", 11197, "-", DateTime.Now + 2))
        SQL1.AddNonQueryToBatch("INSERT INTO tblClientGivenMedicine_Temp (JobDate, DrugID, Notes, Created) VALUES (?, ?, ?, ?)", Array("12/20/2025", 11197, "-", DateTime.Now + 2))
        Dim SenderFilter As Object = SQL1.ExecNonQueryBatch("SQL")
        Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
        Log("NonQuery: " & Success)
        SQL1.ExecNonQuery2("UPDATE tblClientGivenMedicine_Temp SET Notes = ? WHERE rowid = (SELECT rowid FROM tblClientGivenMedicine_Temp WHERE JobDate = ? AND DrugID = ? ORDER BY Created DESC LIMIT 1)", Array As Object("555", "12/20/2025", 11197))
        Dim res As ResultSet = SQL1.ExecQuery("SELECT * FROM tblClientGivenMedicine_Temp ORDER BY rowid")
        Do While res.NextRow
            Log($"${res.GetInt("rowid")}|${res.GetString("JobDate")}|${res.GetInt("DrugID")}|${res.GetString("Notes")}|${res.GetInt("Created")}"$)
        Loop
        res.Close
    Else
        'Delete the DB for next test
        File.Delete(FileDir, "data.db")
    End If
End Sub

 

[ilan]

why do you need ORDER BY and LIMIT if you UPDATE a record in your db?
make sense to use them only when you read from your db using SELECT

 

[aeric]

why do you need ORDER BY and LIMIT if you UPDATE a record in your db?
make sense to use them only when you read from your db using SELECT

I believe it is intended as a "shortcut" to combine these 2 queries into 1.

Dim res As ResultSet = SQL1.ExecQuery2("SELECT rowid FROM tblClientGivenMedicine_Temp WHERE JobDate = ? AND DrugID = ? ORDER BY Created DESC LIMIT 1", Array As Object("12/20/2025", 11197))
Do While res.NextRow
    SQL1.ExecNonQuery2("UPDATE tblClientGivenMedicine_Temp SET Notes = ? WHERE rowid = ?", Array As Object("555", res.GetInt("rowid")))
Loop
res.Close

The database table was badly designed and contains duplicate rows.

 

[hatzisn]

why do you need ORDER BY and LIMIT if you UPDATE a record in your db?
make sense to use them only when you read from your db using SELECT

I also have never used ORDER BY and LIMIT in an UPDATE Query. Intuitively I would say that this approach would be needed in a FIFO situation where you insert records with an increasing ID and at some point you want to update the first 10 records that are "not read" to "read" or something similar.

 

[ilan]

I believe it is intended as a "shortcut" to combine these 2 queries into 1.

Dim res As ResultSet = SQL1.ExecQuery2("SELECT rowid FROM tblClientGivenMedicine_Temp WHERE JobDate = ? AND DrugID = ? ORDER BY Created DESC LIMIT 1", Array As Object("12/20/2025", 11197))
Do While res.NextRow
    SQL1.ExecNonQuery2("UPDATE tblClientGivenMedicine_Temp SET Notes = ? WHERE rowid = ?", Array As Object("555", res.GetInt("rowid")))
Loop
res.Close

The database table was badly designed and contains duplicate rows.

this is the only thing that make sense.

otherwise you dont use WHERE clause and instead of update all records you limit the record you want to update or the WHERE clause is very global and you get many results back and then you also want to update only 1 or more records you specify with LIMIT and ORDER BY but to tell you the truth when i UPDATE a record i know which one i want to update and usually it is a single one so never used LIMIT and ORDER BY in UPDATE OR INSERT statements but it should work according to sqlite tutorial page.

i thing he is missing OFFSET [int] at the end.

should look like this:

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

 

[aeric]

this is the only thing that make sense.

otherwise you dont use WHERE clause and instead of update all records you limit the record you want to update or the WHERE clause is very global and you get many results back and then you also want to update only 1 or more records you specify with LIMIT and ORDER BY but to tell you the truth when i UPDATE a record i know which one i want to update and usually it is a single one so never used LIMIT and ORDER BY in UPDATE OR INSERT statements but it should work according to sqlite tutorial page.

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.