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
どうぞよろしくお願いします。
No.6ベストアンサー
- 回答日時:
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でリネームしていますが、
実テーブルならリネームいりません。
分かりやすく解説していただいてありがとうございます。非常に勉強になりました。
色々と立て込んでいたので回答するのが遅くなってしまいました。
No.4
- 回答日時:
トリガーを使った例を念の為上げておきます
//準備1、テーブルの作成
//テーブルを2つ作ります
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、トリガーの設定
//トリガーを3つ作ります
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
- 回答日時:
簡単な例
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
- 回答日時:
考え方は2つ
一つはその都度SQLでデータを集計しデータ更新はしない
もうひとつは発注テーブルを更新すると同時に納品テーブルも更新する
つまり納品テーブルのフラグをたてないと何度も同じ納品データをつかって
発注データが更新されてしまいます。
おなじ発注番号で、同じ商品を2行以上にわけて発注するケースが想定されるため
命題のテーブル構成では実用に耐えません
かならず発注テーブルは明細行情報をもち、納品テーブルは商品に対して
入庫するのではなく明細行に対して入庫してください
No.1
- 回答日時:
順番にやりましょう。
(1)発注番号,商品番号毎の、総納入数を求めるSQLを書いてみましょう
(2)発注テーブルと (1) を結合して、発注残数を求めるSQLを書いてみましょう。
(3) 「Update Join」で検索してみましょう。他のテーブルやクエリを使って、指定テーブルを更新するSQLが見つかるはずです。
それを基にして、(2)から発注テーブルを更新するSQLを考えましょう。
別の言語からSQL発行している場合なら、(1)ができれば、
(1)の結果を取得
↓
UPDATE 発注テーブル SET
納品済数=XXX, 発注残数=発注数-XXX
WHERE 発注番号=YYY AND 商品=ZZZ
って感じのSQLを実行、とう手もあります。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Amazon Amazonで発送が遅い理由。 5 2022/04/12 20:39
- その他(ネットショッピング・通販・ECサイト) ネットショッピング(再質問) 1 2022/10/07 11:41
- 財務・会計・経理 取引先の先方から注文した商品に対してこちらが発行する納品書や請求書を業務用・販売用・消耗品などに科目 1 2022/07/18 02:18
- Amazon アマゾン 7 2022/06/11 11:03
- その他(ネットショッピング・通販・ECサイト) ネットショッピング 6 2022/10/07 10:45
- Excel(エクセル) Excel 関数 vlookupなどの使い方について質問です。 シート1に品番、商品名、単価、発注条 6 2022/06/15 19:16
- Visual Basic(VBA) ACCESS DAO で不要なテーブルのフィールド(列)の削除 4 2022/06/23 12:13
- 経営情報システム accessでの請求管理について 12 2022/06/11 16:20
- 財務・会計・経理 取引先の先方から今後は注文した商品に対して弊社が発行する納品書や請求書を業務用・販売用・消耗品などに 1 2022/07/18 13:21
- 日用品・生活雑貨 おとといの発注失敗しました。明日まで特売なのですが雑貨全般土日の分を多めにおととい発注したのですがい 3 2023/05/27 20:29
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
「マスタ」と「テーブル」の違...
-
2つのテーブルから条件に一致...
-
重複するキーから一番古い年月...
-
続.ORACLEのSELECTのソートに...
-
商品コード番号を入力すると商...
-
Accessでフィールドを比較した...
-
SQL文について(片方のテーブル...
-
VIEWでテーブルの集計結果...
-
PLSQLの識別子エラー
-
sqlのupdate文で質問です。 テ...
-
行方向のデータを横に並べる
-
エクセル
-
ACCESS2007 フォーム 「バリア...
-
数百万件レコードのdelete
-
[ BETWEEN ] vs [ >= AND <= ]
-
accessテーブル作成クエリを実...
-
オラクルではできるのにSQLSERV...
-
データの二重表示の原因
-
2つの項目が重複するレコード...
-
accessのロック
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
「マスタ」と「テーブル」の違...
-
2つのテーブルから条件に一致...
-
重複するキーから一番古い年月...
-
ACCESS 一つのフィールドに複...
-
PLSQLの識別子エラー
-
主キーの変更
-
Accessでフィールドを比較した...
-
SQL 2つのテーブルとSUBSTRING...
-
続.ORACLEのSELECTのソートに...
-
行方向のデータを横に並べる
-
下記のsqlで取得されるレコード...
-
VIEWでテーブルの集計結果...
-
update文で質問です。 下記の条...
-
[Oracle] UPDATE分の副問い合わ...
-
片方だけ抽出する方法(SQL)
-
自分自身への矢印
-
連番のMin, Maxを取得したい
-
Accessユニオンクエリーで2つ...
-
日付の最大値を検索条件にする方法
-
Inner join と Left joinの明...
おすすめ情報
納品済数と発注残数をひとつのSQL文で同時に更新するのは難しそうですね。
それでは、SQL文を2つに分けて、最初に納品済数を更新し、次に発注残数を更新するというのは通常の更新方法としてOKなのでしょうか?