[SQL]あるテーブルのキーに合致する別テーブルの値を更新する

2016.03.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

バッチ処理等を作成していると、あるテーブルのキーに合致する別テーブルの値を更新したいことがあるかと思います。このような場合、手続き型のプログラムでは以下の様な処理になるかと思います。
(あくまで一例です。他にも様々な記述方法があるかと思います。また文法的に正しいのかも検証しておりません。)

ids = Src.ids # あるテーブルのキーを取得して格納する

ids.each do |id| # キーをループする
  data = Dest.where(["id = ?", id]) # 更新対象を別テーブルより一件ずつ抽出する
  if data
    data.point = 200 
    data.update # 別テーブルの値を一件ずつ更新する
  end
end

このように、あるテーブルのキーに合致する別テーブルのデータを更新する処理をSQLではどうするのかについて書いていきます。

SQLの実装

今回行うこと

以下の様な「src」「dest」の2テーブルがあるとします。キーはどちらも「yyyymmdd」です。

srcテーブル

yyyymmdd(キー) point1 point2
20151201 100 200
20160101 100 200
20160102 300 400
20160103 500 600

destテーブル

yyyymmdd(キー) point1 point2
20160101 null null
20160103 null null

今回はdestテーブルの「point1」「point2」をsrcテーブルの値で更新することにします。更新条件は「dest」「src」テーブルの「yyyymmdd」が一致することです。また更新対象のyyyymmddは2016年01月のものとしましょう。

更新を行うSQL

SQLは以下のようになります。

update dest
set
    point1 = S.point1,
    point2 = S.point2
from
(
    select
        yyyymmdd,
        point1,
        point2
    from
        src
    where
        yyyymmdd >= to_date('20160101', 'YYYYMMDD')
    and
        yyyymmdd <= to_date('20160131', 'YYYYMMDD')
) S
where
    dest.yyyymmdd = S.yyyymmdd
;

データベースの種類によって微妙な違いはあるかと思いますが、大まかな方法は変わらないかと思います。
ポイントとしては

  • 6〜17行目でsrcテーブルのデータを算出し、別名(ここでは「S」)をつける
  • 18、19行目でdestテーブルの更新条件としてキー(yyyymmdd)を指定する
  • 3、4行目でdestテーブルのpoint1、point2がsrcテーブルの値で更新されるように指定する

です。

また今回はsrc、destと異なるテーブルでしたが、一つのテーブル内であるカラムの合計値を別カラムに保持するようなSQLも可能です。(その場合は取得元のテーブル = 12行目がsrcテーブルではなくdestテーブルとなります。)

まとめ

今回の例については、SQLのパターンを知っていれば直に実装できますが、知らないと以外と考えつき難いものの一つのように思えます。バッチ処理におけるSQLの一つのパターンとして参考になれば幸いです。