在redshift中基于自我表查找更新表 [英] Update table based on self table lookup in redshift

问题描述

我有下表

id   email   mgr_email   mgr_id
-------------------------------
1    email1   email2    
2    email2   email3
3    email3   email4

我想通过将mgr_email与电子邮件匹配来从id列中填充mgr_id列，如下所示:

I want to populate the column mgr_id from id column by matching mgr_email with email, just like below:

id   email   mgr_email   mgr_id
-------------------------------
1    email1   email2      2
2    email2   email3      3
3    email3   email4      

以下查询为我提供了postgres所需的结果:

The below query gets me the required result in postgres:

update mytable t1 set mgr_id=t2.id from mytable t2 where t1.mgr_email=t2.email

但是当我尝试进行红移时，会出现以下错误:

But when I try in redshift it gives me the below error:

ERROR:  syntax error at or near "t1"
LINE 1: update mytable t1 set mgr_id=t2.id from mytable t2 where t1.mg...
                       ^

如何在redshift中执行此操作?

How do I do this in redshift ?

推荐答案

虽然有点丑陋，但实际上您必须使用自联接编写子查询，然后才可以从中更新表:

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

这篇关于在redshift中基于自我表查找更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！