多表关联更新

--用优惠表里面的70006569的优惠的开始时间 来更新lik.temp_yangmm_1115_discnt 的开始时间。

--这就出现问题了第一个问题 同一个用户的70006569 优惠的开始时间可能有好几个 取哪一个? 这就需要Rank()函数来解决。

--第二个问题更新的时候会出现无法将Null值插入.这个问题是因为目标表里需要更新的记录数和条件筛选出来的记录数不一样。

目标表记录>条件表。如果不写where exists 条件他会把 能在条件里面找到的记录全都更新为指定值。找不到的就更新为Null。

所以要写上where exists 条件。限制一下更新表的范围。

update lik.temp_yangmm_1115_discnt dtd
   set dtd.start_date =
       (select t.ydate
          from (select rank() over(partition by user_id order by td.start_date desc) rk,
                       td.user_id yuser,
                       td.start_date ydate
                  from ucr_crm2.tf_f_user_discnt td
                 where (td.user_id in
                       (select distinct tp.user_id
                           from lik.temp_yangmm_1115_discnt tp
                          where td.user_id = tp.user_id) and
                       td.discnt_code = 70006569)) t
         where t.rk < 2
           and dtd.user_id = t.yuser)
 where exists (select 1
          from (select rank() over(partition by user_id order by td.start_date desc) rk,
                       td.user_id yuser,
                       td.start_date ydate
                  from ucr_crm2.tf_f_user_discnt td
                 where (td.user_id in
                       (select distinct tp.user_id
                           from lik.temp_yangmm_1115_discnt tp
                          where td.user_id = tp.user_id) and
                       td.discnt_code = 70006569)) t
         where t.rk < 2
           and dtd.user_id = t.yuser);