mysql从查询结果中更新表_mysql 把select结果update更新到表中,从查询结果中更新数据...

发布于:2021-09-13 17:08:26

逻辑:两张表连接获取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







相关资源:MySQL SELECT同时UPDATE同一张问题发生及解决

相关推荐