Sql文のUpdateと副問い合わせで困っています。

質問

お世話になっております。Sql初心者でUpdate文の書き方で困っております。ご教授の程宜しくお願い致します。

下記のような２つのテーブルがあります。
関係はTotalが合計を表すテーブルでDetailが明細を表すテーブルです。OTとNoの組み合わせで一意となります。TotalテーブルのTotalAmountはDetailテーブルのAmountの合計です。

行いたいUpdateは『TotalAmountとTotalの合計が合わないもののみTotalAmountをOTとNoで紐ずくDetailテーブルのAmountの合計で更新する』です。

■テーブル名：Total
-------------------------------------------------------
列名) OT No TotalAmount
-------------------------------------------------------
20 100000 0
20 100001 0
20 100002 10

■テーブル名：Detail
-------------------------------------------------------
列名) OT No Amount
-------------------------------------------------------
20 100000 10
20 100000 10
20 100001 20
20 100001 30
20 100002 5
20 100002 5

■作成したSql
update Total set TotalAmount = (select Sum(Amount) from Detail

Inner Join Total on Detail.OT = Total.OT and Detail.No = Total.No

group by Total.TotalAmount,Total.OT,Detail.OT,Total.No,Detail.No

having Total.TotalAmount <> sum(Detail.Amount) )

where OT = (select OT from Detail) and No = (select No from Detail) and

TotalAmount <> (select sum(Amount) from Detail)

いろいろ試行錯誤しながら作成しましたが『サブクエリは複数の値を返しました　～　』というエラーが出てしまいます。

つっこみどころ満載だと思いますがご教授の程宜しくお願い致します。

回答No.3

jamshid6

＞この部分は何を行っているのでしょうか？
＞＞isnull(d.TotalAmount,0)

そもそもleft outer joinにしている理由は「Totalにはあるけど、（削除されたなどで）Detailにはないというケース」を想定してのものです。
Detailが万一なくなっていれば、やはりTotalAmountは0にしたいでしょうから。

上記のケースではDetail側はデータがないので、NULLになります。
ISNULLは「NULLならばゼロにする」という関数です。
これにより上記ケースでもTotalAmountを0にできます。

回答No.2

jamshid6

分かりやすく考えるには、一旦Detailを集計してからJOINした方がよいですね。

update Total
set TotalAmount=isnull(d.TotalAmount,0)
from Total t
left outer join
(select OT,No,sum(Amount) TotalAmount from Detail
group by OT,No) d ON d.OT=t.OT and d.No=t.No
where t.TotalAmount<>isnull(d.TotalAmount,0)

補足

なるほどです。ありがとうございます。
更新もきれいにできました。

1つ質問です。
この部分は何を行っているのでしょうか？
＞isnull(d.TotalAmount,0)

宜しくお願いします。

回答No.1

t_ohta

そもそも1回のクエリーでTotalテーブル全行のTotalAmountを更新する事自体が無理な気がします。
set TotalAmount = (...) の部分のサブクエリーは where句が無いため group byしか結果の組み合わせ分（複数）の値が返ってくるので、どの値にアップデートしたらいいのか特定できなくてエラーになります。
※ サブクエリーだけ一回実行してみるとわかると思います。

まず、TotalテーブルからOTとNoとTotalAmountの各値を取得しておき

update Total set TotalAmount = (select Sum(Amount) from Detail where OT = [OTの値] and No = [Noの値]) where Detail where OT = [OTの値] and No = [OTの値] and TotalAmount <> (select Sum(Amount) from Detail where OT = [OTの値] and No = [Noの値]);

をぐるぐる回すのがいいのではないでしょうか。

お礼

ご指摘ありがとうございます。

特に↓部分なるほどです。
＞set TotalAmount = (...) の部分のサブクエリーは where句が無いため group byしか結果の組み合わせ分（複数）の値が返ってくるので、どの値にアップデートしたらいいのか特定できなくてエラーになります。