Update with Where, Order by and Limit does not work

Question

I am using SQLite 3.

When I input the following query

UPDATE MyTable Set Flag = 1 WHERE ID = 5 Order By OrderID DESC LIMIT 1;

I will always get an error:

near Order By, syntax error

I cannot figure out what is the problem with my query

Answer 1

To use LIMIT and ORDER BY in an UPDATE or DELETE statement, you have to do two things:

• Build a custom version of the sqlite3.c amalgamation from source, configuring it with the --enable-update-limit option.
• Compile that custom sqlite3.c into your project with SQLITE_ENABLE_UPDATE_DELETE_LIMIT defined to 1.

Answer 2

"Order By OrderID DESC LIMIT 1" is for selecting top one ordered result so you should use it in select query. you should do a subquery where you first get the id and then update it:

UPDATE  MyTable 
SET Flag = 1 
WHERE (ID,OrderID) IN (SELECT ID,OrderID
FROM MyTable 
WHERE ID = 5 
ORDER BY OrderID DESC LIMIT 1);

Demo