SQLite - 根据 row_number() 的结果更新同一表的列 [英] SQLite - Update column of the same table, based on result from row_number()

问题描述

经过多次尝试，我能够生成一个带有我想要的值的 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
)

现在，我想用 "row_number() over win - 1"

例如，

• 在第一行，我想用值 0 更新 "order" 列(原始值 13).
• 在第 2 行，我想用值 1 更新 "order" 列(原始值 14).
• ...等等.

• In the 1st row, I would like to update "order" column (original value 13) with value 0.
• In the 2nd row, I would like to update "order" column (original value 14) with value 1.
• ... and so on.

我可以知道，这样做的正确 SQLite 语句是什么?谢谢.

到目前为止我尝试过的方法是

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
    )
);

但是，这将使 ALL 行order"，具有 row_number() 的第一行超过 win - 1" 值，即 0.

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

推荐答案

您的代码缺少表行和子查询行之间的链接.
我会这样写更新方法:

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");

这将使 "order" 列中的值是唯一的.

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

这篇关于SQLite - 根据 row_number() 的结果更新同一表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！