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：キー保存されていない表にマップする列は変更できません」となります。この「キー保存されていない表」というのが結合の１：ｎとなるときの親表のことです。

 今日は以上まで

にほんブログ村

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

知人の方が最近アメリカのラスベガスやグランドキャニオンなどに行ってきたということで、写真をいろいろと見せてもらったのですが、上の写真はグランドキャニオンで撮ってきたものです。

グランドキャニオンは、表層に近いほうで2億5千万年前、深層部では20億年前の地層と言われていて、それだけの長い期間の地層を見れるのは、世界的にもここだけらしいです。
谷までの深さは、1600m というところもあるとのことで、崖から落ちたら一巻の終わりですね。
奇跡とも言えるあまりの自然の壮大なスケールに、人生観も変わってしまうほどだと話に聞きますが、私も一生に一度は是非訪れてみたいものです。
写真は、観光スポットでガードレールのような柵があるところですが、下はまさに断崖絶壁になっていますね。ただ、この景色を直接見たら、本当にすごいでしょうね！

＜本日の題材＞
UPDATE ～ FROM（他のテーブルのデータを基に更新）

SQL Serverでの仕事をしているときに、こういうSQLの書き方もあるんだと気が付いた内容について上げてみたいと思います。
SQL Serverでは他のテーブルのデータに基づくデータの更新をUPDATE ～ FROM テーブルというかたちで実現できるということを、わりと最近になって知りました。Oracleでは上記の構文はUPDATE句にはなかったので、なかなか気が付かなかったのですが、今回はそれについて取り上げてみたいと思います。

構文は、

UPDATE  tbl_A  SET
    tbl_A.col1 = tbl_B.col1
FROM tbl_A
INNER JOIN tbl_B
  ON  tbl_A.key_column = tbl_B.key_column;

例）
仮に、顧客の毎日の購買履歴を記録するテーブルが「顧客日次購買」として存在する場合に、顧客の過去の購入に関する集計用のテーブル「顧客購買集計」に対して、累計購入金額を前日の購入金額をプラスして更新することを、上記の構文を元に実行してみます。

「顧客日次購買」テーブルの定義：
顧客ID            VARCHAR(10)
購入日              DATE
購入金額              DECIMAL(10)
※主キーは、顧客ID, 購入日

「顧客購買集計」テーブルの定義：
顧客ID            VARCHAR(10)
累計購入金額       DECIMAL(12)
※主キーは、顧客ID

一昨日までの顧客毎の累計金額を登録した「顧客購買集計」テーブルに、昨日分の購入金額をプラスするということで、累計購入金額を更新する処理を行うとします。

まず、一昨日までの累計購入金額を確認します。

SELECT * FROM dbo.顧客購買集計
 ORDER BY 顧客ID;

 昨日の購入があった顧客の、「顧客日次購買」テーブルのデータを確認します。

SELECT * FROM dbo.顧客日次購買
 WHERE 購入日 = CONVERT(VARCHAR, getdate()-1, 111)
 ORDER BY 顧客ID;

 「顧客購買集計」テーブルに、昨日分の購入金額をプラスして、累計購入金額を更新する処理を行います。

UPDATE dbo.顧客購買集計 SET
  累計購入金額 =累計購入金額 + dbo.顧客日次購買.購入金額
FROM dbo.顧客購買集計
INNER JOIN dbo.顧客日次購買
   ON dbo.顧客購買集計.顧客ID = dbo.顧客日次購買.顧客ID
  AND dbo.顧客日次購買.購入日 = CONVERT(VARCHAR, getdate()-1, 111);

更新後の「顧客購買集計」テーブルの結果を確認します。

SELECT * FROM dbo.顧客購買集計
 ORDER BY 顧客ID;

 4人の顧客(K0000011,K0000021,K0000043,K0000076)については、昨日の購入金額分が累計購入金額に足されていることが確認できました。

同様の処理をOracleで行う場合、同じ設定のSQL文ではエラーになります。

UPDATE 顧客購買集計 SET
  累計購入金額 = 累計購入金額 + 顧客日次購買.購入金額
FROM 顧客購買集計
INNER JOIN 顧客日次購買
   ON 顧客購買集計.顧客ID = 顧客日次購買.顧客ID
  AND 顧客日次購買.購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD');

 Oracleでは、このケースでは以下のようなかたちになるかと思います。

UPDATE 顧客購買集計 SET
  累計購入金額 = 累計購入金額 +
                  (SELECT 購入金額
                     FROM 顧客日次購買
                    WHERE 顧客ID = 顧客購買集計.顧客ID
                      AND 購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD'))
 WHERE EXISTS (SELECT 'x' FROM 顧客日次購買
                WHERE 顧客ID = 顧客購買集計.顧客ID
                  AND 購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD'));

 ※上記はSQL Serverでも同様の処理が可能です。

一応、更新後の結果を確認すると

SELECT * FROM 顧客購買集計
 ORDER BY 顧客ID;

 先ほどのSQL Serverと同様の結果が得られました。

今日は以上まで

にほんブログ村