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;
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
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.