使用Oracle sys_guid()生成的ID更新列值时遇到的一个问题

        以下sql,本意是想按vessel_cd, syear, smonth, senddate分组，每组生成一个ID，然后把这个生成的ID更新到每一级数据的m_tab_id的列。执行完后，结果发现相同vessel_cd, syear, smonth, senddate的数据行m_tab_id列会存在不一样的值，如图。百思不得其解。

update ami_month_oil_lube t
   set t.M_TAB_ID =
       (select id
          from (select sys_guid() id, vessel_cd, syear, smonth, senddate
                  from ami_month_oil_lube
                 where vessel_cd is not null
                   and syear = 2018
                 group by vessel_cd, syear, smonth, senddate) t1
         where t1.vessel_cd = t.vessel_cd
           and t1.syear = t.syear
           and t1.smonth = t.smonth
           and t1.senddate = t.senddate)
 where vessel_cd is not null
   and syear = 2018
   and smonth = 2;
commit;
select fileid, M_TAB_ID, vessel_cd, syear, smonth, senddate
  from ami_month_oil_lube
 where vessel_cd = 'OKA'

        第二天突然想到，sys_guid()每执行一次，产生的值都会不一样。同组数据的m_tab_id值不一样，说明更新这一组数据的m_tab_id值时，可能是分了多次去执行来更新。为了避免这种情况出现，改进了一下sql,先把按组生成的ID保存下来，然后再执行Update。这样，终于全部的数据都更新正确了。

create table ami_month_oil_main_tem as
  select sys_guid() id, vessel_cd, syear, smonth, senddate
    from ami_month_oil_lube
   where vessel_cd is not null
     and syear = 2018
   group by vessel_cd, syear, smonth, senddate;
   
update ami_month_oil_lube t
   set t.M_TAB_ID =
       (select id
          from ami_month_oil_main_tem t1
         where t1.vessel_cd = t.vessel_cd
           and t1.syear = t.syear
           and t1.smonth = t.smonth
           and t1.senddate = t.senddate)
 where vessel_cd is not null
   and syear = 2018
   and smonth = 2;
   
drop table ami_month_oil_main_tem;

commit;

select fileid, M_TAB_ID, vessel_cd, syear, smonth, senddate
  from ami_month_oil_lube
 where vessel_cd = 'OKA'