mysql select結果updateをテーブルに更新し、クエリー結果からデータを更新

1007 ワード


論理:2枚のテーブル接続finishinの重量をsaleに挿入する.
 
UPDATE sale
INNER JOIN (
	SELECT
		sale.FNo,
		sale.FEntryID,
		(finishin.FQty) AS qty
	FROM
		sale,
		finishin
	WHERE
		sale.FNo = finishin.FNo
	AND sale.FEntryID = finishin.FEntryID
	ORDER BY
		sale.FNo
) sale2 ON sale2.FNo = sale.FNo
AND sale2.FEntryID = sale.FEntryID
SET sale.FqtyIn = sale2.qty

ロジック:timefinishinテーブルには同じFBillNo,FEntryIDのようなデータが複数あり、パケットでqty総重量を求め、新しいテーブルとsalesorderinfoとなって連表する
UPDATE salesorderinfo
INNER JOIN (
	SELECT
		salesorderinfo.FBillNo,
		salesorderinfo.FEntryID,
		(timein.FqtyIn) AS qty
	FROM
		salesorderinfo,
		(
			SELECT
				*, SUM(timefinishin.FQty) AS FqtyIn
			FROM
				timefinishin
			GROUP BY
				FBillNo,
				FEntryID
		) timein
	WHERE
		salesorderinfo.FBillNo = timein.FBillNo
	AND salesorderinfo.FEntryID = timein.FEntryID
) sale2 ON sale2.FBillNo = salesorderinfo.FBillNo
AND sale2.FEntryID = salesorderinfo.FEntryID
SET salesorderinfo.FqtyIn = sale2.qty