使用引用要更新的列的临时表更新基表[REDSHIFT]

Question

我在下面的例子中遇到了问题。

我有 2 张表，快照和暂存。暂存包含以下列：

|person_id|column_changed|new_value|
|1        |     color    | orange  |
|1        |     sport    | football|

快照包含以下列：

|person_id| color| sport |
|1        | blue | tennis|

我需要使用临时表中的new_value更新快照表中的列(暂存changed_column中的值(。

下面的查询是我将在此简化示例中手动执行的操作。

update snapshot a
set 
a.color='orange',
a.sport='football'
from staging b
where a.person_id=b.person_id

有没有办法相对引用所需的列，而不是手动提供更新列+字段？ 有没有办法在集合谓词中引用 a.'columns_changed'=b.'new_value'？

Answer 1

有两种方法可以做你想做的事情。 首先是多次更新：

update snapshot s
    set color = st.new_value
    from staging st
    where st.person_id = s.person_id and st.column_changed = 'color';
update snapshot s
    set sport = st.new_value
    from staging st
    where st.person_id = s.person_id and st.column_changed = 'sport';

或者，预先聚合并一次完成所有操作：

update snapshot s
    set color = coalesce(st.color, s.color),
        sport = coalesce(st.sport, s.sport)
    from (select st.person_id,
                 max(case when st.column_changed = 'color' then st.new_value end) as color,
                 max(case when st.column_changed = 'sport' then st.new_value end) as sport
          from staging st
          group by st.person_id
         ) st
    where st.person_id = s.person_id;

(这实际上假定新值永远不会NULL。

挑战是什么？ 好吧，加入staging会导致某些行多次更新 - 例如一次用于颜色，一次用于运动。 不幸的是(或者幸运的是(，update不会累积。 只有一个被任意执行。

所以，你的第一种方法是行不通的。 以上两种方法是我想到的。 也许还有第三条路。