副問い合わせの書き方について

質問

以下のようにテーブルを作成され、データが
格納されています。

CREATE TABLE IDLIST(
MAINID number(1),
SUBID number(1),
FLAG number(1),
NAME varchar(10)
);

MAINID SUBID FLAG NAME
---------- ---------- -- --------------------
1 1 1 佐藤様
1 2 0 佐藤さん
1 3 0 佐藤
2 1 1 田中さん
2 2 0 田中

このデータにおいて、FLAGが1であるNAMEをその他の
同一IDにコピーしたいと考えています。
つまり、期待する実行結果は以下のようになります。

MAINID SUBID FLAG NAME
---------- ---------- -- --------------------
1 1 1 佐藤様
1 2 0 佐藤様
1 3 0 佐藤様
2 1 1 田中さん
2 2 0 田中さん

SQL文を以下のように考えてみましたが、

update IDLIST
set NAME = (
select NAME
from IDLIST
where MAINID = (
select MAINID
from IDLIST
where FLAG = 1)
AND FLG = 1
);

この場合はMAINIDが複数のとき、副問い合わせにて
結果が複数あるため、エラーが発生します。
複数返ってくる結果をどのように処理してあげれば
よいか困っています。
是非とも１つのSQLで実現させたいと考えています
ので、知恵をお貸し頂きたいと思います。
よろしくお願い致します。

回答No.1

deadlock · ベストアンサー

手元にOracleがないので、きちんと確認はしていませんが、概ね下記の通りです。
(1)の条件で、同一MAINIDのレコードの中からFLAG=1を探すようにしています。
(2)の条件では最初の一レコードだけ使用するようにしています。Oracle9iなら、Firstなどでも大丈夫だと思います。
(3)の条件は、FLAG=1のレコードが存在するMAINIDのレコードのみ更新するようにしています。これがない場合、FLAG=1が存在しないMAINIDを持つデータのNAMEにはNULLがセットされます。

UPDATE
　　IDLIST T1
SET
　　NAME = (
　　　　SELECT
　　　　　　NAME
　　　　FROM
　　　　　　IDLIST T2
　　　　WHERE
　　　　　　T2.MAINID = T1.MAINID -- (1)同じIDのものからだけ
　　　　　　AND T2.FLAG = 1
　　　　　　AND ROWNUM = 1 -- (2)最初の一行のみ使用
　　)
WHERE
　　-- (3)この条件を消すと、FLAG=1のレコードが見つからない
　　-- 時にNULLが設定されてしまう。
　　EXISTS (
　　　　SELECT
　　　　　　'X'
　　　　FROM
　　　　　　IDLIST T3
　　　　WHERE
　　　　　　T3.MAINID = T1.MAINID
　　　　　　AND T3.FLAG = 1
　　　　　　AND ROWNUM = 1
　　)

お礼

deadlockさん、ご回答ありがとうございます。
非常にわかりやすく、参考になりました。
本当にありがとうございました。