Is there a trick to using window functions to SET columns in an UPDATE? Here is the query I'd like to run:
UPDATE profiles
SET score_tier = percent_rank()
OVER (PARTITION BY site_id ORDER BY score ASC)
WHERE score IS NOT NULL
But that gives me an error on Postgres 9.1:
ERROR: cannot use window function in UPDATE
This alternate version works, but is messier and slower:
UPDATE profiles p
SET score_tier = x.perc
FROM (SELECT id,
percent_rank() OVER (PARTITION BY site_id ORDER BY score ASC) AS perc
FROM profiles p2
WHERE score IS NOT NULL) AS x
WHERE
p.id =
x.id AND p.score IS NOT NULL
That second version is also prone to deadlocks if another job is updating the profiles table at the same time, even with a query like this:
UPDATE "profiles" SET "updated_at" = '2012-11-13 21:53:23.840976' WHERE "profiles"."id" = 219474
Is there any way to reformulate this query so it is cleaner, faster, and not prone to deadlocks?
Thanks,
Paul
--
_________________________________
Pulchritudo splendor veritatis.
