SQLパズル：解答と解説

SQL

　それでは、SQLパズル：NULLの埋め立て の解答と解説です。明智さん、みーむさん、kasaiさん、お見事でした。私が考えたのも、明智さんとkasaiさんの相関サブクエリバージョンと同じもの。



/* 理事長の答え */

UPDATE OmitTbl

   SET 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 < OmitTbl.seq

                                 AND O2.val IS NOT NULL))

 WHERE val IS NULL;

　考え方は、valがNULLの行について、その行と同じkeycolを持つ行の集合から、

  1. valがNULLではない
  2. 自分より小さいseqを持つ

という二つの条件を満たす集合に絞り、その中で最大のseqを探す、ということ。これが一番深いサブクエリの中で行っていることです。ちょっと細かいけど、気をつけるポイントとしては、更新対象のテーブルについて、「UPDATE OmitTbl O」のように相関名をつけてはいけない、ということ。標準SQLでは、UPDATEやDELETEの対象テーブルに相関名をつけることを認めていないからです。実際、これをやってしまうとPostgreSQLではエラーになります。相関サブクエリの中でもそのまま「OmitTbl」と書かないといけない。Oracleなどでは気を利かせて相関名もOKなので、私もちょくちょくこの禁則を破ってしまうことがある。

　明智さんの二つ目とみーむさんの解答は興味深いですね。これは思いつかなかった。勉強になります。

　SQLについて考えるときはつい検索クエリに重点をおきがちだけど、実務では更新も重要な比率を占めるものだから、そのうちどこかで更新SQLについても代表的なパターンをまとめておくのもいいかもしれないなー。まだ面白い問題も幾つかあるし、これで記事２，３本分はいけそう。

　それにしてもはてなのコメント欄でスペースが削除されるのは困ったものですね。コードが読みにくくなってしまう。
　それでは皆さんまた来月。ご機嫌よう。