第2回更新なんてこわくない! (1)NULLが存在するテーブル

はじめに

SQLの「Q」が「Query(問い合わせ⁠)⁠」の略であることからもわかるように、SQLという言語は誕生の時点から、データベースから情報を引き出すことを主な用途として考えられていました。実際、私たちが業務で使用するSQLの大半はSELECT 文であると言ってよいでしょう。

反面、UPDATEやDELETEといった更新のための機能について、詳細に取り上げられる機会はあまりありません。近年は標準SQLにMERGE文が追加されるなど便利な拡張が施されている分野ですが、意外に盲点となっていてその強力さが十分に理解されていないのです。

というわけで、今回のテーマは「更新」です。前回と同様、いくつかのケーススタディを通じて、更新SQLの応用方法とそのロジックを詳しく見ていきたいと思います。

稼働環境は次のとおりで、括弧内が実際に動作確認を行ったバージョンです。

Oracle9i以降(Oracle10.2.0.1)
SQL Server 2005以降(SQLServer 2005)
DB2(DB2 9.5)
PostgreSQL(PostgreSQL 8.3)
MySQL 5.0以降(MySQL 5.0)

NULLが存在するテーブル

NULLの埋め立て

さて、まずは更新SQLにおける相関サブクエリの使い方から見ていきましょう。表1のようなサンプルテーブルを考えます。keycol(キー)+seq(連番)で一意な、何の変哲もないテーブルです。注目してほしいのは、valがNULLの行です。これは、本当は値はあるのだけれど、前のレコード(同じkeycolで連番が1つ前)と同じ値のため省略されているのです。紙をパンチして電子データを作る場合など、タイプ回数を減らすためにこういう省略措置がよく行われます。

表1 OmitTbl:埋め立て前

keycol(キー)	seq(連番)	Val(値)
A	1	50
A	2
A	3
A	4	70
A	5
A	6	900
B	1	10
B	2	20
B	3
B	4	3
B	5
B	6

人間ならそういうルールと知っていればすぐわかりますが、データベースにこういうデータを投入してvalの値を集計したい場合は、このままのテーブルでは使い物になりません。律儀に全行について値を埋めてやる必要があります(このテーブルのままSELECT文を工夫して解決する方法もありますが、それは本稿の主旨から外れます⁠)⁠。

そこで、表2のようにNULLの行を「埋めた」テーブルを作ってください。まず、更新対象がval列がNULLの行に限られることは明らかですから、UPDATE文のWHERE句の条件が「val IS NULL」であることはすぐにわかりますね。あとは更新対象となる各行について、

同じkeycolを持つ
自分より小さいseqを持つ
valがNULLではない

という3つの条件を満たす集合に絞り、その中で最大のseqを探す、という条件を満たす行を探し出し、その行のval値をSETすればよいのです。ですから、答えはリスト1のようになります。先ほどの3つの条件すべてが、この1つのUPDATE文の中に盛り込まれていることがおわかりいただけるでしょう。

表2 OmitTbl:埋め立て後

keycol(キー)	seq(連番)	Val(値)
A	1	50
A	2	50 ← 埋めた
A	3	50 ← 埋めた
A	4	70
A	5	70 ← 埋めた
A	6	900
B	1	10
B	2	20
B	3	20 ← 埋めた
B	4	3
B	5	3 ← 埋めた
B	6	3 ← 埋めた

リスト1 OmitTbl のUPDATE文

UPDATE OmitTbl
 SET val = (SELECT val
 FROM OmitTbl O1
 WHERE O1.keycol = OmitTbl.keycol ―1同じkeycolを持つ
 AND O1.seq = (SELECT MAX(seq)
 FROM OmitTbl O2
 WHERE O2.keycol = OmitTbl.keycol
 AND O2.seq ―2自分より小さいseqを持つ
 AND O2.val IS NOT NULL)) ―3valがNULLではない
WHERE val IS NULL;

逆にNULLを作成する

ちなみに、埋め立て後のOmitTbl(表2)を出発点にして、埋め立て前の「省略バージョン」のテーブル(表1)に変換することも、ほとんど同じ考え方で可能です。リスト2がそのUPDATE文のコードです(SELECT文もまったく同じ考え方で作れます⁠)⁠。

リスト2 埋め立ての逆演算SQL(UPDATE文)

UPDATE OmitTbl
 SET val = CASE WHEN val
 = (SELECT val 
  FROM OmitTbl O1 
  WHERE O1.keycol = OmitTbl.keycol 
  AND O1.seq 
  = (SELECT MAX(seq) 
  FROM OmitTbl O2 
  WHERE O2.keycol = OmitTbl.keycol 
  AND O2.seq ←スカラ・サブクエリ全体をCASE 式の引数としている
 THEN NULL
 ELSE val END;

先ほどの1〜3の条件に合致する行に関してはNULLを、そうでない行に関しては当該行のval値を選択するような分岐をCASE式で表現しているのがこの方法のポイントです。引数の中にサブクエリ全体を組み込むことのできるCASE式の柔軟性が際立ちます。これが可能なのは、このサブクエリが値を1つだけ返すスカラ・サブクエリであるからです。

ちなみに、上のSQLは、MySQLのように更新時のサブクエリで自己参照ができないDBではエラーとなってうまくいきません。こういうケースへの対処法は、「⁠(⁠4)同じテーブル内の別の行から値を計算する(2009年8月5日公開⁠)⁠」で見ることにします。