ITコーディネータのシュウです。

 前回に続いて、アメリカに行ってきた知人の方から頂いた写真で、フーバーダムというラスベガスのそばにある有名なダムの写真です（走っている車の中から撮ったということ）。

ダムによってコロラド川がせき止められたことでできた湖（ダム湖）はミード湖と呼ばれ、貯水量は400億トン、日本にある2,500基のダムの貯水量の合計が250億トンであり、琵琶湖の貯水量が280億トン程度ということなので、如何に大きいかということがわかりますね。
このダムは、1931年世界恐慌のときに、ニューディール政策の一環として着工されて1936年に竣工し、当時は世界一の水力発電だったとのこと。その後、世界にはもっと大きなダムもできているということですが、よくこんな大きなダムを作れるものだと、人間の力は本当にすごいと思います。

それから、先日の父の日は、子供たちから一品ずつ、チョコとかちょっとしたものでしたが、プレゼントを頂きました。一番下の息子はカードを作ってくれて、とても素直な表現がうれしかったです。妻もいつもよりは少しだけ豪華な食事を用意してくれて、うれしい1日となりました。

＜本日の題材＞
UPDATE ～ FROM（その２）

前回、SQL ServerでのUPDATE文で、他のテーブルのデータに基づくデータの更新をUPDATE ～ FROM テーブルというかたちで実現できるということを説明しました。
そのときに、Oracleでの対応の仕方として、例では EXISTS句を使ったものを取り上げましたが、今回はインラインビュー（ビューでもよい）を使ったかたちでの更新を取り上げてみたいと思います。

構文としては、
UPDATE (
    SELECT A.col2 Acol2, B.col2 Bcol2
    FROM tbl_A  A
    JOIN tbl_B  B
    ON A.col1 = B.col1
    )
SET Acol2 = Bcol2;

ただし、UPDATEを行うのは更新可能なビュー（インラインビューも含む）でなければならず、また、テーブルの結合が1：n の場合には、1の親表の列のデータは更新できないということです。
（前回の例である、「顧客購買集計」テーブルに、昨日分の購入金額をプラスして、累計購入金額を更新する処理では、テーブルの結合が１：nの場合に該当するため、このビューの更新の方法ではできません）

ちなみに、以下のような場合は更新できないビューになります。
　・GROUP BY句を使用したビュー
　・式により定義したビュー
　・DISTINCT句を使用したビュー
　・グループ関数を使用したビュー
　・擬似列ROWNUMを使用したビュー

例を以下に示します。
例）
社員マスタの入社日のデータが何らかの問題で消えてしまったとします。対応として、以前バックアップしていたテーブルの日付に戻したいという場合があるとします。当然、現在の社員マスタと、以前バックアップしていた社員マスタはDBのレイアウトは同じなので、データは1対１の関係です。
この場合、Oracleでの上記のやり方で更新するかたちでは以下になります。

 UPDATE (
    SELECT A.入社日, B.入社日 BK_入社日
    FROM 社員マスタ  A
    JOIN 社員マスタ_BAK  B
    ON A.社員番号 = B.社員番号
    )
SET 入社日 = BK_入社日;

 まず、社員マスタのデータを確認します。

SELECT 社員番号, 社員名, 入社日
  FROM 社員マスタ
 ORDER BY 社員番号;

入社日の値がNULLになっています。
次に、同様にして社員マスタ_BAKのデータ（入社日）を確認します。

 上記で示したように、社員マスタ_BAKの入社日のデータで社員マスタの入社日の値を更新します。

 更新後の社員マスタの入社日のデータを確認します。

 

 入社日がセットされているのが確認できます。
念のため、社員マスタの入社日と社員マスタ_BAKの入社日に差がないことを確認します。
上記から、社員マスタと社員マスタ_BAKがともに12件で同じ件数であることを利用して、下記の結果が同じく12件であれば、入社日に差がないことが確認できます。

 上記の入社日の更新を、SQL Serverで実施する場合は、前回説明したように、

UPDATE dbo.社員マスタ SET
  入社日 = dbo.社員マスタ_BAK.入社日
FROM dbo.社員マスタ
INNER JOIN dbo.社員マスタ_BAK
   ON dbo.社員マスタ.社員ID = dbo.社員マスタ_BAK.社員ID;

とするか、JOINを使わずに

UPDATE dbo.社員マスタ SET
  入社日 = SB.入社日
FROM dbo.社員マスタ_BAK SB
WHERE dbo.社員マスタ.社員ID = SB.社員ID;

とすることもできます。
以下は実際に実行したところです。

 念のため結果を確認すると、

 件数が12件で同じため、社員マスタの入社日は社員マスタ_BAKの入社日と同じものに更新されたことがわかります。

※ちなみに、前回の例としての「顧客購買集計」テーブルに、昨日分の購入金額をプラスして、累計購入金額を更新する処理に、今回のやり方で更新する方法で実行してみると、以下のようにエラーが出ますね。

 UPDATE (
   SELECT A.累計購入金額 A_累計購入金額, B.購入金額 B_購入金額
    FROM 顧客購買集計 A
     JOIN 顧客日次購買 B
       ON A.顧客ID = B.顧客ID
    WHERE B.購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD')
       ) C
 SET C.A_累計購入金額 = C.A_累計購入金額 + C.B_購入金額;

 エラーは「ORA-01779：キー保存されていない表にマップする列は変更できません」となります。この「キー保存されていない表」というのが結合の１：ｎとなるときの親表のことです。

 今日は以上まで

にほんブログ村