比對並Update的SQL - MS SQL version

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

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

DECLARE c Cursor FOR
  select pd.tankid, pd.bodid, pd.sampletstamp
  from newbod.dbo.tblQCThickMeasurements bod, ProcessData.dbo.tblQCThickMeasurements pd
  where bod.tankid = pd.tankid
  and bod.bodid = pd.bodid
  and bod.sampletstamp = pd.sampletstamp
  and pd.qualcopied = 0;
Open c;
IF (@@CURSOR_ROWS<>0) ---判斷cursor開啟狀況
 BEGIN  
  DECLARE @tankId VARCHAR(50), @BodId VARCHAR(50), @sampleTS Datetime;  
  Fetch NEXT FROM c INTO @tankId, @BodId, @sampleTS;

  While (@@FETCH_STATUS <> -1)--當陳述式失敗，或資料列超出結果集時停止迴圈
   BEGIN
    update ProcessData.dbo.tblQCThickMeasurements set
      qualcopied=1 
    where tankid = @tankId
    and bodid = @BodId
    and sampletstamp = @sampleTS;
    Fetch NEXT FROM c INTO @tankId, @BodId, @sampleTS;
   END;
 END;
CLOSE c;
DEALLOCATE c;
GO

比對並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;

MS SQL 2000以下版本如何做出row_num的效果?

MS SQL 2005含以上的版本有個 row_number() 可以讓使用者容易的做查詢出的資料加上序號，可是MS SQL 2000就不支援了。
由於這樣的需求簡單寫了下方的code，可以做出序號效果，前題是id欄位是唯一。

select (select count(1) from table1 c
  where c.id <= table1.id) as row_number, 
  table1.*     
from table1