Update table with window function

Question

I've table in Redshift with duplicated row that i want to delete,

for that I created filed Id and i want to update him to delete duplicated rows I'm trying to run this query but it doesn't work

update mr_usage
set id=row_number () over (partition by uid,date(ts),title order by ts)

I received the following error:

ERROR: cannot use window function in UPDATE

I'm looking for a way to update that field

Answer 1

The other possible solution (without CTE) is using UPDATE .. FROM syntax with subquery directly

UPDATE mr_usage outer
SET id = sub.new_id
FROM (
    SELECT 
        id, ROW_NUMBER() OVER (PARTITION BY uid, date(ts), title ORDER BY ts) AS new_id
    FROM
        mr_usage
) sub
WHERE outer.id = sub.id

But it is also available since PostgreSQL 8.4.