sql - 更新红移表上的 is_latest 列

Question

我正在尝试更新由以下更新语句分组的 redshift 表上的列is_latest，但收到一个错误，即在更新语句中不允许使用窗口函数。解决此问题的最佳方法是什么？sourcesource_primary_key

update my_schema.production_log
set is_latest = 
case when run_start_time = max(run_start_time) over (partition by 
source,   source_primary_key)
then ‘t’ else ‘f’
end

标签： sqlamazon-redshift

Answer 1

Redshift 支持FROM语句中的子句update。尝试这个：

update my_schema.production_log
    set is_latest = (case when run_start_time = max_run_start_time
                          then 't' else 'f'
                     end)
    from (select source, source_primary_key, max(run_start_time) as max_run_start_time
          from my_schema.production_log
          group by source, source_primary_key
         ) pl2
    where pl2.source = production_log.source and
          pl2.source_primary_key = production_log.source_primary_key;