How to overwrite a row_number value in SQL?

June 23, 2022

How to overwrite a column in SQL (Redshift) ?

The following query gives me an error Target table must be part of an equijoin predicate

UPDATE table1
SET rank = temp.new_rank
FROM (
      SELECT ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY date) AS new_rank
      FROM table1
      ) temp;

>Solution :

You need a WHERE clause that matches each row of the table with a row of the subquery:

UPDATE table1 AS t1
SET rank = t.new_rank
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY date) AS new_rank
  FROM table1
) AS t
WHERE t.session_id = t1.session_id AND t.date = t1.date;

I use the columns session_id and date in the WHERE clause, but if there is another column, like a unique id or any other primary key it would be better to use that.