[GENERAL] window functions in an UPDATE

[Paul Jungwirth]

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.