SQL：重複を削除した場合の別テーブルの更新

質問

下記のようなことを行いたいのですが、もう数十時間悩んでおり、このままだとクビになります。

以下のようなテーブルが２つあります

■テーブルA
顧客ID なまえ 内線
――――――――
001、　佐藤、 001　※同じ（これだけ残す）
002、　鈴木、 002
003、　田中、 003　
004、　佐藤、 001　※同じ（削除）
005、　佐藤、 004
006、　鈴木、 005
007、　佐藤、 001　※同じ（削除）

■テーブルB
伝票ID 顧客ID 購入物
001、　001、　ガム　※対応する顧客は残る
002、　003、　チョコレート
003、　004、　塩　　※対応する顧客が残らない
004、　006、　ガム
005、　002、　塩
006、　007、　塩　　※対応する顧客が残らない

テーブルAの佐藤4人中3人は、名前も内線も同じなので
顧客IDは一番小さいものだけ残し、あとは同一とみなし重複行を削除します。

そうするとテーブルBの伝票IDの001、003、006の顧客IDは全部が佐藤だったのですが
重複を削除してしまったため、003と006に対応する顧客がテーブルAから消えてしまいます。

なので、テーブルAの重複を削除するときに、同時にテーブルBの顧客IDも
一緒に一番小さい顧客IDに更新したいのです。

どうか宜しくお願いいたします。

回答No.4

taka_tetsu · ベストアンサー

#1です。
update文の文法間違えていました。

update テーブルB set 顧客ID=(
select A集計.顧客ID
from テーブルA A,
(select min(顧客ID) 顧客ID, なまえ, 内線 from テーブルA group by なまえ, 内線) A集計
where A.なまえ = A集計.なまえ
and A.内線 = A集計.内線
and テーブルB.顧客ID =A.顧客ID
)

でいけるはずです。Oracle8i 8.17で確認済みです。
※where句の条件少し変えました。

>色々調べて「A集計」というのはテーブルAの別名なのかな？と思うのですが
>いまいちピンときません

「A集計」は、
select min(顧客ID) 顧客ID, なまえ, 内線 from テーブルA group by なまえ, 内線
というselect文の抽出結果に対する別名です。selectの抽出結果をテーブルに見立てています。インラインビューというものです。

回答No.3

k_o_r_o_c_h_a_n

未確認です。

・テーブルBの更新
update B
set 顧客ID=
(select min(A2.顧客ID) from A A1,A A2,B where B.顧客ID=A1.顧客ID and A1.なまえ=A2.なまえ and A1.内線=A2.内線)

・テーブルAの削除
delete A A1
where exists(select 1 from A A2 where A1.顧客ID>A2.顧客ID and A1.内線=A2.内線 and A1.なまえ=a2.なまえ)

お礼

回答していただいてありがとうございます。とても参考になりました。

回答No.2

taka_tetsu

>・テーブルBの削除
>delete from テーブルA
>where not exists(select なまえ, 内線 from テーブルA A2 group by なまえ, 内線 having テーブルA.顧客ID = A2.min(顧客ID))

ちょっと間違い。
・テーブルAの削除
delete from テーブルA
where not exists(select なまえ, 内線 from テーブルA A2 group by なまえ, 内線 having テーブルA.顧客ID = min(A2.顧客ID))

回答No.1

taka_tetsu

・テーブルBの更新
update テーブルB
set 顧客ID = A集計.顧客ID
where テーブルB.顧客ID =
(select A.顧客ID from テーブルA A,
(select min(顧客ID) 顧客ID, なまえ, 内線 from テーブルA group by なまえ, 内線) A集計
where A.なまえ = A集計.なまえ and A.内線 = A集計.内線 and A.顧客ID <> A集計.顧客ID)

・テーブルBの削除
delete from テーブルA
where not exists(select なまえ, 内線 from テーブルA A2 group by なまえ, 内線 having テーブルA.顧客ID = A2.min(顧客ID))

これでどうかな？未確認です。

考え方は、
・削除対象の顧客IDと、それに対応する新しい顧客IDを求める。
・テーブルBに存在する削除対象の顧客IDのレコードを、上で求めた新しい顧客IDで更新する。
・テーブルAの削除対象のレコードを削除する。

という感じになるかと。

お礼

深夜なのに早速のご回答ありがとうございます。
上記のSQL文を実際のテーブル名にあわせて実施してみたのですが
以下のようなエラーが出てしまいます。
「ora-00904 "A集計"."顧客ID":無効な識別子です」

SQL文が複雑すぎて何が違うんだか理解できませんでした。
テーブルBの更新の2行目から出てくる「A集計」というのが何をさしているのだか分かりません。

色々調べて「A集計」というのはテーブルAの別名なのかな？と思うのですが
いまいちピンときません