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