使用另一个表的最大日期更新表

这应该可以让您开始(这里的max函数是聚合函数,而不是分析函数):

UPDATE table_a
   SET status = (SELECT MAX(table_b.status) 
                        KEEP (DENSE_RANK FIRST ORDER BY table_b.statusdate DESC)
                   FROM table_b
                  WHERE table_a.location = table_b.location
                    AND table_b.statusdate <= table_a.trandate);

这将更新中的所有行 table_a ,即使中没有前一行 table_b ,在这种情况下,将状态更新为空。如果只想更新 表A 有相应匹配的 表B 可以添加筛选器:

UPDATE table_a
   SET status = (SELECT MAX(table_b.status) 
                        KEEP (DENSE_RANK FIRST ORDER BY table_b.statusdate DESC)
                   FROM table_b
                  WHERE table_a.location = table_b.location
                    AND table_b.statusdate <= table_a.trandate)
 WHERE EXISTS (SELECT NULL
                 FROM table_b
                WHERE table_a.location = table_b.location
                  AND table_b.statusdate <= table_a.trandate);

这是一个具有分析功能的版本。如图所示,它将更新表中的所有行。要更新特定行,请添加筛选器。

update table_a t1 set status = (
       select distinct
              first_value(t2.status) over (partition by t1.location, t1.trandate order by t2.statusdate desc)
       from temp_b t2 
       where t1.location = t2.location
       and t2.statusdate <= t1.trandate );