Update Table Based On Self Table Lookup In Redshift

I have the below table, id email mgr_email mgr_id ------------------------------- 1 email1 email2 2 email2 email3 3 email3 email4 I want to populate the

Solution 1:

It's a bit ugly but you really have to write the subquery with self-join and only then update the table from it:

update mytable
set mgr_id=t.id
from (select t1.email,t2.id
    from mytable t1 
    join mytable t2
    on t1.mgr_email=t2.email
) t
where mytable.email=t1.email;

as said in comments, it's the particular case of more generic update from table syntax

Solution 2:

Use sub query for self join:

Try this:

update mytable 
    set mgr_id=t2.id 
    from (select id, email from mytable) t2 
    where mytable.mgr_email=t2.email