SQLのUPDATE文に関して教えて下さい

質問

SQLのUPDATE文に関して教えて下さい
まだまだ初心者で勉強中なのですが、ご教授をお願い致します。

２つのテーブルがあり同じ条件になったレコードのある項目を別のもう片方の項目にセットしたいのですが上手くいきません。
具体的には以下の通りです。

【テーブル１】
品番、　規格、　取引先、　日付
A1　　　XXX　　T001　　　2010/01/01
A1　　　YYY　　T002　　　（空白）
B1　　　XXX　　T001　　　（空白）
C1　　　ZZZ　　T003　　　（空白）

【テーブル２】
品番、　規格、　取引先、　受入日
A1　　　XXX　　T001　　　2010/01/01
A1　　　YYY　　T002　　　2010/03/01
B1　　　XXX　　T001　　　（空白）

テーブル１の「日付」が（空白）のレコードを対象に、
テーブル１の「品番＋規格＋取引先」とテーブル２の「品番＋規格＋取引先」が同じな場合、
テーブル１の「日付」項目にテーブル２の「日付」項目をUPDATEしたいと考えています。
結果を以下のようにしたいです。

【テーブル１】
品番、　規格、　取引先、　日付
A1　　　XXX　　T001　　　2010/01/01
A1　　　YYY　　T002　　　2010/03/01　　←ここが今回更新される
B1　　　XXX　　T001　　　（空白）
C1　　　ZZZ　　T003　　　（空白）

以前この場で教えて頂いたものを参考に以下のような感じで考えていたのですがエラーになります。

update テーブル１ set テーブル１.日付 = テーブル２.受入日
where テーブル１.日付 Is Null and
(concat(concat(品番, 規格), 取引先)) in (select (concat(concat(品番, 規格), 取引先)) from テーブル２) ;

ERROR at line 1:
ORA-00904: "テーブル２"."受入日": invalid identifier

set文のところの記述方法がわかりません。

宜しくお願い致します。

回答No.6

dda167 · ベストアンサー

>>また主キーにすることで記述を変えることが出来る
主キーである必要はありません。
簡単に書きすぎたので、少し説明しますと……
No.1の2番目のUPDATE文の場合、
更新される方（この場合テーブル１）の行に対して
更新する方（この場合テーブル２）に該当する行が複数存在すると、
どのデータで更新すればよいのかOracleにはわからないわけです。
テーブル２が一意になることを保障してやる必要があります。
保障する方法の1つが主キーを貼ることなのですが、
テーブル２のキー項目（品番＋規格＋取引先）で
一意索引を作成してもかまいません。

# うまく説明できてないな……
# 「更新可能なビュー」「ORA-01779」あたりで検索してください。

>>in条件が使えない理由
特定のデータでうまくいかないのであれば、そのデータを……
Oracleがエラーメッセージを返すのであれば、そのメッセージを……
うまくいかない状況を挙げてみてはいかがでしょう。

# 両テーブルの主キーは何だろう？

回答No.5

k_o_r_o_c_h_a_n

>”いろいろ問題”に関して少しだけ具体的な事例があれば教えて下さい。

key1,key2,key3
AAA,AAA,AAA
AA,AA,AAAA
AA,AAA,AAAA
A,A,AAAAAAA

key||key2||key3やconcat(concat(key1,key2),key3)は、Aの並び9個になります。
異なるレコードにも関わらず、区別がつきません。

(key1,key2,key3) in (select key1,key2,key3 from hoge)
は、当然区別します。

なぜ、in条件が使えないのか、未だに理解できていませんが、in条件が使えない、
または効率的に使わないほうが良い場合には、existsを使うのが良いでしょう。
同様に、not in条件が非効率な実行計画となる場合も、not existsを試行してみると良いかと思います。
オプティマイザが賢ければ、同じ計画となるべきところでも、実際は異なるケースも多々あるので、
都合の良い方を選択的に採用すると良いと思いますよ。

お礼

k_o_r_o_c_h_a_n様、

非常に分かりやすいご説明ありがとうございます。
同じ並びになってしまう可能性があるということが非常によく分かり理解できました。
in条件が使えない理由は色々試してみます。
まだまだ最初の一歩を踏み出したところですのでこれからもご教授お願い致します。

回答No.4

dda167

>>複数項のIN条件がエラー
まさか……
キー（と思われる）項目にNULLが入っていたりしないでしょうねえ(^^;

補足

dda167様、
ご回答ありがとうございます。
NULL確認しましたが、存在しませんでした。
全てのレコードのキー項目にデータが入っています。

回答No.3

k_o_r_o_c_h_a_n

＃２ですが・・
SQL書いているうちに、＃１と思い切りかぶりました。忘れてください。

回答No.2

k_o_r_o_c_h_a_n

前回の質問で、締め切り済みでコメントできなかったのですが・・
CONCATで単一文字列にして、IN条件とするのは、いろいろ問題があります。
なぜ、複数項のIN条件がエラーになったのか気になりますが、どうしても使えないなら、
existsでの書き換えが可能です。

例えば、今回のSQLの場合だと、次のような条件式への書き換えになります。
（更新項目の右辺は、今回の質問に対する回答に相当します）

update テーブル１
set テーブル１.日付 =
(
select テーブル２.受入日 from テーブル２
where テーブル１.品番=テーブル２.品番 and テーブル１.規格=テーブル２.規格 and テーブル１.取引先=テーブル２.取引先
)
where テーブル１.日付 Is Null and
exists(
select 1 from テーブル２
where テーブル１.品番=テーブル２.品番 and テーブル１.規格=テーブル２.規格 and テーブル１.取引先=テーブル２.取引先
);

補足

k_o_r_o_c_h_a_n様、

前回に引き続きご支援ありがとうございます。
前回自分なりに出来たと思っていたのですが、まだまだでした。
ご回答頂いた中に
>CONCATで単一文字列にして、IN条件とするのは、いろいろ問題があります。
とありますが、”いろいろ問題”に関して少しだけ具体的な事例があれば教えて下さい。
何か懸念点があればconcat方法を止めないといけないと思いました。
宜しくお願い致します。

回答No.1

dda167

テーブル２の受入日がNULLの場合は除外しています。

update テーブル１ set 日付 = (
select 受入日 from テーブル２
where テーブル２.品番 = テーブル１.品番
and テーブル２.規格 = テーブル１.規格
and テーブル２.取引先 = テーブル１.取引先
)
where テーブル１.日付 is null
and exists (
select * from テーブル２
where テーブル２.品番 = テーブル１.品番
and テーブル２.規格 = テーブル１.規格
and テーブル２.取引先 = テーブル１.取引先
and テーブル２.受入日 is not null
);

たとえば、品番＋規格＋取引先が主キーの場合、
以下のように書くこともできます。

update (
select t1.日付, t2.受入日
from テーブル１ t1, テーブル２ t2
where t1.品番 = t2.品番
and t1.規格 = t2.規格
and t1.取引先 = t2.取引先
and t1.日付 is null
and t2.受入日 is not null
)
set 日付 = 受入日;

補足

dda167様、

ご回答ありがとうございます。
”exists”を使ってサンプル頂いた内容で更新できることを確認しました。
また主キーにすることで記述を変えることが出来ることも分かりました。
まだまだ初心者で日々勉強の毎日ですので、また何かありましたらご支援をお願い致します。