UPDATE ~ FROM(その2)


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

0530-143

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

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

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

<本日の題材>
UPDATE ~ FROM(その2)

前回、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の親表の列のデータは更新できないということです。
(前回の例である、「顧客購買集計」テーブルに、昨日分の購入金額をプラスして、累計購入金額を更新する処理では、テーブルの結合が1:nの場合に該当するため、このビューの更新の方法ではできません)

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

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

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

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

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

ora_社員マスタ_入社日なし

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

ora_社員マスタbak_入社日あり

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

ora_社員マスタ_戻し更新

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

 ora_社員マスタ更新後

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

ora_件数一致確認

 上記の入社日の更新を、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;

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

sqlsv_update_from例2

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

sqlsv_update_例2_後結果比較

 件数が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_update_顧客購買エラー3

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

 今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>