2つのテーブルの間で条件に合致した行を合計して引きたい

質問

すいません、適切なタイトルが浮かばず、あやふやなタイトルになってしまいました・・・

MySQLバージョン4.1.16を使っています。
2つのテーブルがあって、
table1には「id,x,y,value1」、table2には「x,y,value2」、
の列がそれぞれあるとします。

サンプル
[table1]
id,x,y,value1
1　1　1　100
2　1　2　160
3　1　3　120
[table2]
x,y,value2
1　1　20
1　1　30
1　3　10

table1のidが1で、table1のx,yとtable2のx,yがそれぞれ等しい場合にのみ
table1のvalue1からtable2のvalue2（列でxが同じ、yが同じ行があったならvalue2を合計して）を
引くということがやりたいのです。
そこで以下のようにして試してみたのですが、

UPDATE table1 t1, table2 t2 SET t1.value1 = t1.value1 - t2.value2 WHERE t1.id = '1' AND t1.x = t2.x AND t1.y = t2.y;

これだとtable2の行が１つだと希望どおりに動作するのですが、
2つ以上だと1つ目の行しか引いてくれません。
サンプルに当てはめると、[table1]のid=1のvalue1は80になります（希望は50）
どのようにSQLを記述すれば良いのでしょうか？

回答No.2

chukenkenkou · ベストアンサー

更新対象はtable1なので、1行しか作用してくれなかったようですね。

以下のSQLでは、どうでしょうか？

UPDATE table1 t1,
(select x,y,sum(value2) as value2 from table2 group by x,y) as t2
SET t1.value1=t1.value1-t2.value2
WHERE t1.id=1 AND t1.x=t2.x AND t1.y=t2.y;

お礼

ご返答ありがとうございます。
そのやり方でできました。ありがとうございます。

回答No.1

yambejp

table2をGROUP BY x, yしてvalueのsumをとってから
引き算してはどうでしょうか？

お礼

ご返答ありがとうございます。
自分としてはsumのとり方（？）がいまいち分からなかったのですが、
#2で回答してくださったことにより理解できました。
なるほど、そういうことだったんですね。