CODEWITHSUNDEEP
sqlite
Cheok Yan Cheng
Cheok Yan Cheng

Reputation: 42806

SQLite - Update column of the same table, based on result from row_number()

After several tries, I was able to generate a row_number() column with the value I wished.

select "order", row_number() over win - 1, type, title, body, searched_string from plain_note 

WINDOW win AS (
    order by 
        title desc,
        case when type = 0 then body else searched_string end desc
)

enter image description here

Now, I would like to overwrite entire "order" column, with the value from "row_number() over win - 1"

For instance,

May I know, what is the correct SQLite statement to do so? Thanks.

The approach I had tried so far is

update plain_note set "order" = (
    select row_number() over win - 1 from plain_note 

    WINDOW win AS (
        order by 
            title desc,
            case when type = 0 then body else searched_string end desc
    )
);

However, this will make ALL rows of "order", having the 1st row of "row_number() over win - 1" value, which is 0.

Upvotes: 0

Views: 267

Answers (1)

forpas
forpas

Reputation: 164184

Your code is missing a link between the table's rows and the subquery's rows.
I would write the update method like this:

with cte as (
  select *, row_number() over win - 1 as rn from plain_note

  window win as (order by 
    title desc,
    case when type = 0 then body else searched_string end desc
  )    
)
update plain_note set "order" = (select rn from cte where "order" = plain_note."order");

This will work of the values in the column "order" are unique.

Upvotes: 2

Related Questions