Cannot set Percent_rank() - defaults to 0.0-CodePudding

I'm trying to SET a column, but the result will end up as 0.0 for each row. If I use the same syntax (the select part of it) in SELECT, the results display correctly.

UPDATE table1
SET ranking = (SELECT
PERCENT_RANK() OVER(PARTITION BY city ORDER BY sales DESC)
from table1
group by store_id)

Is it possible to make this work?

CodePudding user response：

The subquery:

SELECT PERCENT_RANK() OVER(PARTITION BY city ORDER BY sales DESC)
from table1
group by store_id

returns 1 row for each store_id and SQLite picks just one and updates with that row's value of PERCENT_RANK() all the rows of the table.

You must correlate the subquery with table1

UPDATE table1
SET ranking = (
  SELECT pr
  FROM (
    SELECT store_id, 
           PERCENT_RANK() OVER(PARTITION BY city ORDER BY sales DESC) pr
    FROM table1
    GROUP BY store_id
  ) t
  WHERE table1.store_id = t.store_id
);

Or, if your version of SQLite is 3.33.0 use the UPDATE...FROM... syntax:

UPDATE table1 AS t1
SET ranking = t.pr
FROM (
  SELECT store_id, 
         PERCENT_RANK() OVER(PARTITION BY city ORDER BY sales DESC) pr
  FROM table1
  GROUP BY store_id
) t
WHERE t1.store_id = t.store_id;