CODEWITHSUNDEEP
sqlsqlitesql-update
alancc
alancc

Reputation: 811

Update with Where, Order by and Limit does not work

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

Upvotes: 4

Views: 773

Answers (6)

kaushik
kaushik

Reputation: 556

Order By statement will not work in update query. You have to use alternate way

UPDATE MyTable Set Flag = 1 WHERE ID = 5 
and OrderId = (select max(OrderId) from MyTable where Id = 5);

If you have used the query like above it will work.

Upvotes: 1

Majva
Majva

Reputation: 150

"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

Upvotes: 3

Steve Chambers
Steve Chambers

Reputation: 39434

SQL

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

Demo

SQLFiddle demo: http://sqlfiddle.com/#!5/6d596/2

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175964

You could use ROWID:

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

db<>fiddle demo

or (ID,OrderID) tuple:

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

db<>fiddle demo2

And if you need to do it in bulk for every ID(SQLite 3.25.0):

WITH cte AS (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY OrderID DESC) AS rn FROM tab
)
UPDATE tab
SET Flag = 1
WHERE (ID, OrderID) IN (
       SELECT ID, OrderID
       FROM cte
       WHERE rn = 1
      );

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Introduction

Below I'm presenting two solutions to solve the issue and perform a proper UPDATE. At the end of each solution there's a live example on sample data included.

  • First does not require you to input any id and works for the entire table by picking the latest orderid for each id and changing it's flag to 1

  • Second requires you to input an id and works only for updating one id on the run

I'd personally go with first solution, but I am not sure about your requirement, thus posting two possibilities.

First solution - update entire table

Explanation here, for code scroll down.

For this we will use Row Value construction (id, orderid) just like for the second solution. It will find the latest row based on orderid and update only that row for given (id, orderid) pair. More on that is included in Explanation of second solution.

We will also need to simulate row_number function to assign each row ranking numbers, to find out which row has the latest orderid for every id and mark then as 1 to be able to pull only those for update. This will allow us to update multiple rows for different ids in one statement. SQLite will have this functionality built in version 3.2.5 but for now, we will work with a subquery.

To generate row numbers we will use this:

select 
  *, 
  (select count(*) from mytable m1 where m1.id = m2.id and m1.orderid >= m2.orderid) as rn
from mytable m2

Then we just need to filter the output on rn = 1 and we have what we need.

That said, the whole UPDATE statement will look like:

Code

update mytable
set flag = 1
where (id,orderid) in (
  select id, orderid
  from (
    select *, (select count(*) from mytable m1 where m1.id = m2.id and m1.orderid >= m2.orderid) as rn
    from mytable m2
    ) m
  where 
    m.rn = 1
    and m.id = mytable.id
  );

Live DEMO

Here's db fiddle to see this solution live on sample data.

Second solution - update only one ID

If you know your ID to be updated and want to run UPDATE statement for only one id, then this will work:

Code

update mytable
set flag = 1
where (id,orderid) in (
  select id, orderid
  from mytable
  where id = 5
  order by orderid desc
  limit 1
  );

Explanation

(id, orderid) is a construction called Row Value for which SQLite compares scalar values from left to right.

Example taken from documentation:

SELECT (1,2,3) = (1,2,3) -- outputs: 1

Live DEMO

Here's db fiddle to see this solution live on sample data.

Upvotes: 0

Shawn
Shawn

Reputation: 52549

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.

Upvotes: 3

Related Questions