sql - unable to write an update statement with left join

Question

my update statement works fine with INNER JOIN but gives me error when I replace it with LEFT JOIN. HOw can I achieve left join here ? I am looking for LEFT JOIN results where if a corresponding rn =2 doesn't exist then I need to update null in the table.

with cte as (
select * from 
( select row_number() over(partition by user_id order by loginDate desc) rn,
        min(loginDate) over(partition by user_id) min_date,
        max(loginDate) over(partition by user_id) max_date,
       dau.* from DAILY_Active_user_table dau ) as foo
where rn <= 2
)

update user_agg_activity 
    SET first_login_date = cte.min_date,
        last_login_date  = cte.max_date,
        prev_login_date =  cte.loginDate,
        date_partition = current_date
    from user_agg_activity uac, cte 
    where  cte.user_id = user_agg_activity.user_id;
    --group by uac.user_id

question from:https://stackoverflow.com/questions/66056106/unable-to-write-an-update-statement-with-left-join

Answer 1

Too many references to the user_agg_activity. The reference in the FROM is entirely different from the reference in the UPDATE, so it results in a Cartesian product. So:

update user_agg_activity uac
    SET first_login_date = cte.min_date,
        last_login_date  = cte.max_date,
        prev_login_date =  cte.loginDate,
        date_partition = current_date
    from cte 
    where cte.user_id = uac.user_id;