CODEWITHSUNDEEP
sqlsqlite
Brad
Brad

Reputation: 163468

UPDATE … FROM syntax with sub-query

I have a table, items, with a priority column, which is just an integer. In trying to do some bulk operations, I'm trying to reset the priority to be a sequential number.

I've been able to use ROW_NUMBER() to successfully generate a table that has the new priority values I want. Now, I just need to get the values from that SELECT query into the matching records in the actual items table.

I've tried something like this:

UPDATE
  "items"
SET
  "priority" = tempTable.newPriority
FROM
  (
    SELECT
      ROW_NUMBER() OVER (
        ORDER BY
          /* pile of sort conditions here */
      ) AS "newPriority"
    FROM
      "items"
  ) AS tempTable
WHERE
  "items"."id" = "tempTable"."id"
;

I keep getting a syntax error "near FROM".

How can I correct the syntax here?

Upvotes: 0

Views: 81

Answers (2)

Brad
Brad

Reputation: 163468

It turns out that the root answer to this specific question is that SQLite doesn't support UPDATE ... FROM. Therefore, some alternative methods are needed.

https://www.sqlite.org/lang_update.html

Upvotes: 0

forpas
forpas

Reputation: 164154

SQLite is not as flexible as other rdbms, it does not support even joins in an update statement.
What you can do instead is something like this:

update items
set priority = 1 + (
  select count(*) 
  from items i 
  where i.id < items.id
)

With this the condition is derived only by the ids.
So the column priority will be filled with sequential numbers 1, 2, 3, ....
If you can apply that pile of sort conditions in this manner, you will make the update work.
Edit.
Something like this maybe can do what you need, although I'm not sure about its efficiency:

UPDATE items
SET priority = (
  SELECT newPriority FROM (
    SELECT id, ROW_NUMBER() OVER (ORDER BY /* pile of sort conditions here */) AS newPriority
    FROM items
  ) AS tempTable
  WHERE tempTable.id = items.id
)

Upvotes: 1

Related Questions