２テーブル間でフィールドの更新、１回のUPDATEでできますか？

質問

いろいろ調べたけど、わからなかったので質問します。

Ａ表
キー、日付
10,NULL
20,NULL
30,NULL

Ｂ表
キー、日付
10,20040905
30,20040906
40,20040907

Oracle（8.1.7）上に上記のような２つの表があり、Ａ表と、Ｂ表でキーが同じだった場合、Ｂ表の日付をＡ表の同じキーの日付フィールドに更新する方法はありますか？（下のような結果になりたい）

Ａ表
キー、日付
10,20040905
20,NULL
30,20040906

１回のＳＱＬ文で更新することはできるのでしょうか？
できるのであれば、教えていただきたいと思います。

回答No.3

mitoneko · ベストアンサー

結論から言うと、ほんとは全く違うんです・・・

　その、#2の補足のＳＱＬが間違ってます。#1のzabesuさんのかかれたＳＱＬと良く比較してください。というか、update文の挙動が理解できていないようですね。
　正しい、sql文、もう一度、全部書きますね。

　UPDATE A表 SET A表.日付 =
(SELECT MAX(B表.日付) FROM B表
　　　　　　　　　　　　　　WHERE A表.キー = B表.キー);

　です。#2の補足が間違っている場所は、副SELECT文のFROM句に「A表」の指定があるかないかです。
　で、これ、まったく挙動が変わります。今回の場合は、付けてはいけません。
　正しいＳＱＬでは、「A表.キー」の値は、UPDATEが今処理しようとしているＡ表の行の値から採取されると思えばわかりやすいでしょう。

　参考までに、正しいUPDATE文の挙動を簡単に説明しておきます。
　ある行の更新、たとえば、キーが10の場合を考えましょう。副SELECT文のWHERE句で、「A表.キー」の値は、今まさに更新しようとしている行の「キー」は10ですから、10です。したがって、Ｂ表の「キー」が10のところをＢ表から探します。そして、その行の日付をUPDATEのSET句に示されるように「A表.日付」に代入します。
　では、キーが20の行を更新するとしましょう。「A表.キー」は２０ですから、「A表.キー=B表.キー」が成立する行はＢ表にはありません。よって、副SELECT文の結果はNULLです。その値が、「A表.日付」に代入されます。

　元の例示のＡ表の日付が全部ＮＵＬＬですから、挙動としてはこれでｏｋです。

　ちなみに、正しい方のＳＱＬだと、実は、ＭＡＸを付ける必要は本来無い「はず」なんです。なぜなら、おそらく、Ａ表.キーと、Ｂ表.キーには、最低でもユニークインデックスがついているはずで、おそらくは名前からしてプライマリーキー指定がついているでしょう。この場合、「A表.キー = B表.キー」を満たすＢ表の行は一行しかないことをデータベースは認識できます。ですから、MAX関数でごまかして１行に見せかける必要はどこにもありません。
　MAX関数が必要なのは、人間としては、SELCT文が成立する行は一行しかないことを知っているんだけれども、データベースには１行と認識できない場合です。複雑なWHERE句を書くとよく発生します。この場合は、しかたがないので、MAX関数やMIN関数で一つしかないとデータベースに対して自己主張してやります。ただし、人間が約束を破ってWHERE句が成立する行が２行以上あれば、データベースは指示通りに最大または最小の値をもって作業を継続します。（簡単な例では、Ａ表とＢ表のキー列にユニーク指定もプライマリーキー指定も無い場合です。でも、この制約は、このデータベースにデータを入力する際の重要なデータチェックの要素となりますから、できれば指定した方がよいでしょうね。）

お礼

おかげさまでうまくいきました。
いろいろと教えて下さってありがとうございました。
ＳＱＬ文について、もっと勉強しようと思います。

回答No.2

mitoneko

#1補足のようなエラーは、SELECT句のフィールドリストを「MAX(B表.日付)」としてしまえば回避できます。1個の値にMAXやMIN関数を適用しても値は変りませんから。
但し、B表のキーが一意である事が条件です。まあこの制約が問題になるようならもともと質問の更新は論理的に不可能ですが…

補足

mitoneko さんのようにやってみました。
「update A表 set 日付 = (select MAX(B表.日付) from A表,B表 where A表.キー = B表.キー);」
を実行した結果、Ａ表の全レコードが更新されました。
これだと
（例）「update A表 set 日付 = '20040908';」
と同じねすよね。

UPDATE文に WHERE句が必要だと思いますが、どのように書いたら良いのでしょうか？

回答No.1

zabesu

確認していないですけど、
update A表 set
日付 = (select B表.日付 from B表 where A表.キー = B表.キー);
というようなSQL文でいきそうな気がします。

補足

update A表 set
日付 = (select B表.日付 from A表,B表 where A表.キー = B表.キー);
でやってみましたが、「ORA-01427 単一行副問合わせにより２つ以上の行が返されます。」と表示されます。

「set 日付=」は１つを返されるようにしないといけないと思うのですが・・・