比對並Update的SQL - Oracle version

系統上線一陣子後因為資料日漸增多，我們會把資料切成online, offline的部分，有時會想將online更新到offline，這時我們把兩個Table的資料做比對，找出相同的資料後，更新offline。

使用下面的SQL可以更新offline資料。

update 
    (select ol.tank_id, ol.bod_id, ol.sampletstamp, ol.qualcopied
    from hisadm.qc_cord_summ su, hisadm.qc_cord_summ_ol ol
    where su.tank_id = ol.tank_id
    and su.bod_id = ol.bod_id
    and su.sampletstamp = ol.sampletstamp
    and ol.qualcopied = 'F'
    and ol.sampletstamp <> to_date('2009/7/26 11:32:00','yyyy/MM/dd HH24:MI:SS')
    and ol.sampletstamp <> to_date('2009/12/8 19:17:00','yyyy/MM/dd HH24:MI:SS')) a set 
a.qualcopied = 'T';

當條件上出現重覆資料時，Oracle就不允許update了，需要自已將重覆測試的資料過濾，自行update不重覆的資料，會使用到cursor如同下方SQL

declare cursor c is select ol.tank_id, ol.bod_id, ol.sampletstamp, ol.qualcopied
                    from hisadm.qc_cord_summ su, hisadm.qc_cord_summ_ol ol
                    where su.tank_id = ol.tank_id
                    and su.bod_id = ol.bod_id
                    and su.sampletstamp = ol.sampletstamp
                    and ol.qualcopied = 'F'
                    and ol.sampletstamp <> to_date('2009/7/26 11:32:00','yyyy/MM/dd HH24:MI:SS')
                    and ol.sampletstamp <> to_date('2009/12/8 19:17:00','yyyy/MM/dd HH24:MI:SS');
begin
  for r_c in c loop  
      update hisadm.qc_cord_summ_ol a set
      a.qualcopied = 'T'
      where a.tank_id = r_c.tank_id
      and a.bod_id = r_c.bod_id
      and a.sampletstamp = r_c.sampletstamp;
  end loop;
end;