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_

Solution 1:

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 -1as rn from plain_note

  window win as (orderby 
    title desc,
    casewhen type =0then body else searched_string enddesc
  )    
)
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.