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と同様の結果が得られました。

今日は以上まで

にほんブログ村