発注テーブルの発注残数を納品テーブルの納品数で更新する

SQLはまだ初心者ですので、教えてください。
発注テーブルに発注数と納品数、発注残数の項目があり、納品テーブルに納品数があります。
納品テーブルの納品数の合計値で発注テーブルの納品済数と発注残数を更新したいのですがどの様なSQL文を書けばよいのでしょうか。

テーブルイメージは以下の様です。
＜発注テーブル＞
発注番号　商品　発注数　納品済数　発注残数
1 A 100 　 0 100
1 B 200 　 0 200

＜納品テーブル＞
発注番号　納品番号　商品　納品数
1 1 A 40
1 1 B 20
1 2 A 40
1 2 B 10

この状態で、納品テーブルの同一発注番号の納品数を商品毎に集計して、発注テーブルの同一発注番号の商品毎の納品済数と発注残数を更新したいのです。

更新後のイメージは以下の様です。
＜発注テーブル＞
発注番号　商品　発注数　納品済数　発注残数
1 A 100 　 80 20
1 B 200 　 30 70

どうぞよろしくお願いします。

質問者からの補足コメント

• 納品済数と発注残数をひとつのSQL文で同時に更新するのは難しそうですね。
  それでは、SQL文を2つに分けて、最初に納品済数を更新し、次に発注残数を更新するというのは通常の更新方法としてOKなのでしょうか？

  No.3の回答に寄せられた補足コメントです。 補足日時：2016/09/05 16:00

No.6 ベストアンサー 回答者： yanadai0419 回答日時： 2016/09/06 15:42

kmeeさんの対応方法が良いかと思いました。

こういうのをいいたいのだと思います。
自分も良くやりますし、クエリも手順を分解して順番追えば簡単に作れますよ。

具体的にはこんな感じですね。
UpdateJoinを使った更新クエリです。
From句を使うのがポイントですね。

@発注番号に更新対象の発注番号をセットし実行すると
その発注番号の発注テーブルのデータが更新されます。

declare @発注 table(発注番号 int,商品 varchar(10),発注数 int,納品済数 int,発注残数 int);
declare @納品 table(発注番号 int,納品番号 int,商品 varchar(10),納品数 int,flg tinyint);
declare @発注番号 int

insert into @発注 values(1,'A',100,0,100),(1,'B',200,0,200);
insert into @納品 values(1,1,'A',40,0),(1,1,'B',20,0),(1,2,'A',40,0),(1,2,'B',10,0);
set @発注番号 =　1

--Update Join
update @発注 set 納品済数 =納品数,発注残数 = 発注数 - 納品数
from @発注 as 発注 inner join

--Updateに必要なテーブルを副問い合わせ
(select 納品.発注番号,納品.商品,sum(納品.納品数)as 納品数 from @発注 as 発注,@納品 as 納品
where 発注.発注番号 = 納品.発注番号
and 発注.商品 =　納品.商品
--更新対象の発注番号を指定
and 発注.発注番号 = @発注番号
group by 納品.発注番号,納品.商品) as 納品

--副問い合わせしたデータの結合条件(inner join のon)
on 発注.発注番号 = 納品.発注番号
and 発注.商品 =　納品.商品

※テーブルにテーブル変数使っているのでASでリネームしていますが、
実テーブルならリネームいりません。

この回答へのお礼

分かりやすく解説していただいてありがとうございます。非常に勉強になりました。
色々と立て込んでいたので回答するのが遅くなってしまいました。

お礼日時：2016/09/16 01:12

No.5 回答者： yambejp 回答日時： 2016/09/05 18:25

あ、申し訳ないですが、あくまでMySQLベースで書いています

他のRDBの場合トリガーの仕様が違うかもしれないので適宜調整してください

No.4 回答者： yambejp 回答日時： 2016/09/05 18:09

トリガーを使った例を念の為上げておきます

//準備1、テーブルの作成
//テーブルを２つ作ります
CREATE TABLE 発注(発注番号 INT,発注明細 INT(3) UNSIGNED ZEROFILL,商品 VARCHAR(10),発注数 INT,納品済数 INT,発注残数 INT,PRIMARY KEY(発注番号,発注明細));
CREATE TABLE 納品(発注番号 INT,発注明細 INT(3) UNSIGNED ZEROFILL,納品番号 INT,納品明細 INT(3) UNSIGNED ZEROFILL,商品 VARCHAR(10),納品数 INT,PRIMARY KEY(納品番号,納品明細));

//準備2、トリガーの設定
//トリガーを３つ作ります

DROP TRIGGER IF EXISTS TRG_NOUHIN_IN;
DROP TRIGGER IF EXISTS TRG_NOUHIN_MOD;
DROP TRIGGER IF EXISTS TRG_NOUHIN_DEL;

DELIMITER //
CREATE TRIGGER TRG_NOUHIN_IN AFTER INSERT ON 納品
FOR EACH ROW BEGIN
UPDATE 発注 SET 納品済数=納品済数+NEW.納品数
,発注残数=発注残数-NEW.納品数
WHERE 発注番号=NEW.発注番号 AND 発注明細=NEW.発注明細;
END;
//
CREATE TRIGGER TRG_NOUHIN_MOD AFTER UPDATE ON 納品
FOR EACH ROW BEGIN
UPDATE 発注 SET 納品済数=納品済数-OLD.納品数+NEW.納品数
,発注残数=発注残数+OLD.納品数-NEW.納品数
WHERE 発注番号=NEW.発注番号 AND 発注明細=NEW.発注明細;
END;
//
CREATE TRIGGER TRG_NOUHIN_DEL BEFORE DELETE ON 納品
FOR EACH ROW BEGIN
UPDATE 発注 SET 納品済数=納品済数-OLD.納品数
,発注残数=発注残数+OLD.納品数
WHERE 発注番号=OLD.発注番号 AND 発注明細=OLD.発注明細;
END;
//
DELIMITER ;

ここからテスト
(1)INSERT INTO 発注 VALUES(1,1,'A',100,0,100),(1,2,'B',200,0,200);
SELECT * FROM 発注;→納入済:Aが80、Bが30

(2)INSERT INTO 納品 VALUES(1,1,1,1,'A',40),(1,2,1,2,'B',20),(1,1,2,1,'A',40),(1,2,2,2,'B',10);
SELECT * FROM 発注;→Aが80、Bが30

(3)DELETE FROM 納品 WHERE 納品番号=2;
SELECT * FROM 発注;→Aが40、Bが100

(4)TRUNCATE 納品;
SELECT * FROM 発注;→Aが40、Bが100のままなので注意!!
※トリガーでコントロールしているテーブルは安易にtruncateしないこと

No.3 回答者： yambejp 回答日時： 2016/09/05 10:51

簡単な例

create table 発注(発注番号 int,商品 varchar(10),発注数 int,納品済数 int,発注残数 int);
insert into 発注 values(1,'A',100,0,100),(1,'B',200,0,200);

create table 納品(発注番号 int,納品番号 int,商品 varchar(10),納品数 int,flg tinyint);
insert into 納品 values(1,1,'A',40,0),(1,1,'B',20,0),(1,2,'A',40,0),(1,2,'B',10,0);

//単純な表示
select
t1.発注番号
,t1.商品,t1.発注数
,t1.納品済数+t2.納品数 as 納品済数
,t1.発注残数-t2.納品数 as 発注残数
from 発注 as t1
inner join (select 発注番号,商品,sum(納品数) as 納品数
from 納品
group by 発注番号,商品
)as t2
on t1.発注番号=t2.発注番号 and t1.商品=t2.商品

更新する場合はプロシージャで処理する必要があるかもしれません

No.2 回答者： yambejp 回答日時： 2016/09/05 10:04

考え方は２つ

一つはその都度SQLでデータを集計しデータ更新はしない
もうひとつは発注テーブルを更新すると同時に納品テーブルも更新する
つまり納品テーブルのフラグをたてないと何度も同じ納品データをつかって
発注データが更新されてしまいます。

おなじ発注番号で、同じ商品を２行以上にわけて発注するケースが想定されるため
命題のテーブル構成では実用に耐えません
かならず発注テーブルは明細行情報をもち、納品テーブルは商品に対して
入庫するのではなく明細行に対して入庫してください

この回答へのお礼

ありがとうございます。
ご指摘の通り、実際は発注明細の行番号で処理をする予定です。

お礼日時：2016/09/05 15:54

No.1 回答者： kmee 回答日時： 2016/09/04 19:07

順番にやりましょう。

(1)発注番号,商品番号毎の、総納入数を求めるSQLを書いてみましょう
(2)発注テーブルと (1) を結合して、発注残数を求めるSQLを書いてみましょう。
(3) 「Update Join」で検索してみましょう。他のテーブルやクエリを使って、指定テーブルを更新するSQLが見つかるはずです。
　それを基にして、(2)から発注テーブルを更新するSQLを考えましょう。

別の言語からSQL発行している場合なら、(1)ができれば、
(1)の結果を取得
↓
UPDATE 発注テーブル SET
納品済数=XXX, 発注残数=発注数-XXX
WHERE 発注番号=YYY AND 商品=ZZZ
って感じのSQLを実行、とう手もあります。

この回答へのお礼

ありがとうございます。
まだ、回答が見つからないので、探してみます。

お礼日時：2016/09/05 07:24