テーブルの集計値を別テーブルに更新したいのですが

質問

mysql＋PHPを利用していますが、表記の件で数日間悩んでおります。
実際に可能な操作かどうかもわかりません。
よいお知恵があったら教えてください。

下のようなテーブルがそれぞれあります。
table1
ID・得意先コード・受注内容・受注金額
1・　　　28　　・　xxxx　・　10000
2・　　　32　　・　aaaa　・　 5000
3・　　　28　　・　YYYY　・　20000
4・　　　15　　・　tttt　・　15000
5・　　　32　　・　kkkk　・　 3000
（得意先コードはtable2のIDです）

table2
ID・得意先名・受注金額合計
15・　ＢＢＢ・
28・　ＣＣＣ・
32・　ＤＤＤ・

PHPファイルを実行する度に、
「table1」の「受注金額」を「得意先コード」ごとに集計した各値を、「table2」の各「受注金額合計」にそれぞれ更新していきたいと思っています。

色々調べたところ以下の構文が紹介されていたので試してみました。
Update table2 set 受注金額合計 = (select sum(受注金額) from table1 INNER JOIN table2 ON table1.得意先コード = table2.ID)

すると「#1064 - You have an error in your SQL syntax」というエラーが出てしまいました。select以降の構文が良くないらしいのですが、私の書き方が悪いのか、それすらもわからない状態です。

MySQLは4.0.24、PHPは4。レンタルサーバーを利用しています。
なにとぞよろしくお願いします。

回答No.5

nick9090

＞PHP側で計算というのは、SQLのテーブルに計算結果を更新せず、ブラウザで計算結果を表示させるのみ。ということでしょうか。

いえいえ、PHPを使って、table1から得意先コードごとの金額を抽出して合計し、table2の「受注金額合計」にその合計額を入れるのです。

「PHPファイルを実行する度に…」と書いていらっしゃいますので、そのPHPファイルの末尾にでも、上記の処理を書いておくだけです。

ごくごくシンプルなことで、何も悩む部分はないと思うのですが・・・？

お礼

＞いえいえ、PHPを使って、table1から得意先コードごとの金額を抽出して合計し、table2の「受注金額合計」にその合計額を入れるのです。

知識不足ですみません。
この方法がわからなかったので質問させていただいたのですが、SQL以外で方法があるのでしょうか。

回答No.4

nick9090

計算としては非常に簡単なものですが、それをSQLで書くとちょっとややこしくなるので、その程度ならPHP側で計算したらどうでしょうか。

レコード数にもよりますが、大した負荷ではないと思います。

お礼

nick9090さん、ご回答ありがとうございます。

PHP側で計算というのは、SQLのテーブルに計算結果を更新せず、ブラウザで計算結果を表示させるのみ。ということでしょうか。

回答No.3

yambejp

あら・・・そうですか、私の手元には3.23系と5.0系の環境しかなく
4.0系の環境がないため検証ができずお役に立てず申し訳ないです。

>SUM(kingaku) as totalの「total」とは何を示すのでしょうか

table1のkingakuSUMしたものに別名を付けています。
別名をつけないとそのあとでてくる
set total_kingaku=t1.totalのところで指定ができないので
別名は適当な名前で結構です。

ちなみに以下のように２段階でテンポラリをつくれば、3.23系でもできるます。
REPLACEを使うので、table2のIDにUNIQUE属性をつけておく必要があります。

CREATE TEMPORARY TABLE T1
SELECT client_id,sum(kingaku) as kingaku
FROM table1
GROUP BY client_id;

CREATE TEMPORARY TABLE T2
SELECT ID,client_name,T1.kingaku
FROM table2 as t2
INNER JOIN T1 ON ID=client_id;

REPLACE table2
SELECT * FROM T2;

お礼

yambejpさん、わざわざテンポラリ作成のSQLを教えていただき、ありがとうございます。

教えていただいたSQLを実行してみたところ、目的どおりの結果が得られました。
UNIQUE属性やREPLACEなど、これまでに使ったことがない物ばかりでした。
PHPで利用するため、複数のSQLがPHPで利用可能かどうかをこれから調べてみたいと思います。

回答No.2

yambejp

ごめんなさい。途中になっちゃいました

こんな感じでどうでしょう？
Update table2
left join (
select 得意先コード,sum(受注金額) as total from table1
GROUP BY 得意先コード
) as t1 on t1.得意先コード=table2.ID
set 受注金額合計=t1.total

お礼

ありがとうございます。
折角回答していただきましたが、質問の際と同じエラーが表示されました。
やはりVerの問題でしょうか。

実際に行ったSQLは、「得意先コード」を「client_id」、「受注金額」を「kingaku」として、以下のように修正したものを実行しました。
UPDATE table2 LEFT JOIN (SELECT client_id,SUM(kingaku) as total FROM table1 GROUP BY client_id) as table1 ON table1.client_id=table2.id SET kingaku_total=table1.total

それと、気になったのはSUM(kingaku) as totalの「total」とは何を示すのでしょうか。補足をいただけるとありがたいです。

回答No.1

yambejp

別テーブルをつかったupdateはバージョン 4.0.4 以降と書いてありますね。

http://dev.mysql.com/doc/refman/4.1/ja/update.html

お礼

yambejpさん、早速のお返事ありがとうございます。

4.04以降ですか。そこまで調べませんでした。
まだ調べてないことが多そうです。
レンタルサーバーを利用している以上、複数テーブルを使うUPDATEは不可能ということでしょうか。残念です。
SELECTで表示させることはできたのですけど。

もう少しがんばって他の方法を考えてみます。