如何使用内部联接

Question

我有这个疑问

Update ProductionDetails P Inner join 
( SELECT Distinct A.BaseCode, A.BaseScale, (A.BaseScale * B.BasePer/100) AS BaseVal, A.TreadCode, A.TreadScale, (A.TreadScale * B.TreadPer/100) as TreadVal, A.InterfaceCode, A.LipCode, A.LipScale, 
    (A.LipScale * B.HsPer/100) as LipVal, A.CenterCode, A.CenterScale, (A.CenterScale * B.CenterPer/100) AS CenterVal, A.StencilNo 
        from productionDetails A inner join 
          BlendMaster B on (A.InterfaceCode = b.Category AND A.BaseCode = B.Base AND A.TreadCode = B.Tread AND A.centerCode = B.center) 
      Where B.Status = yes 
) AS ResTable on (P.StencilNo = ResTable.StencilNo) 
     Set P.BaseValue = ResTable.BaseVal, P.TreadValue = ResTable.TreadVal , P.LipValue = ResTable.LipVal, P.CenterValue = ResTable.CenterVal 

我需要在BaseValue，TreadValue，CenterValue，LipValue字段更新ProductionDetails表中计算值（选择写更新查询查询...）AS ResTable。

如果我写命令从ResTable中选择我得到的价值，但如果我更新它说错误“操作必须使用可更新的查询”。我需要在的MS Access 2013

以下运行，这是对样本数据

CREATE TABLE ProductionDetails(StencilNo Text, LipCode Text, LipScale Text,LipValue Number,BaseCode Text,BaseScale Number,BaseValue Number,InterfaceCode Text,CenterCode Text,CenterScale Number,CenterValue Number,TreadCode Text,TreadScale Number,TreadValue Number) 

Create Table BlendMaster (Category Text, Base Text, BasePer number , HsPer number, Center text, CenterPer number, Tread text, TreadPer number) 

Insert into ProductionDetails (StencilNo, LipCode , LipScale ,BaseCode ,BaseScale , InterfaceCode ,CenterCode , CenterScale , TreadCode ,TreadScale ) 
VALUES ('C160405234', '-', 0,'BFA10',48.44,'BF10+CEG28' , 'CEG28', 36.5, 'TRR51', 52.56) 

Insert into BlendMaster (Category, Base, BasePer ,HsPer, Center , CenterPer , Tread,TreadPer ) 
VALUES ('BF10+CEG28', 'BFA10',25, 25, 'CEG28', 15, 'TRR51', 18) 

Answer 1

我想更新应该这样

UPDATE p 
SET P.basevalue = ResTable.baseval, 
     P.treadvalue = ResTable.treadval, 
     P.lipvalue = ResTable.lipval, 
     P.centervalue = ResTable.centerval 
     from 
#productiondetails P 
     INNER JOIN (SELECT A.basecode, 
            A.basescale, 
            (A.basescale * B.baseper/100)  AS 
            BaseVal, 
            A.treadcode, 
            A.treadscale, 
            (A.treadscale * B.treadper/100) AS 
            TreadVal, 
            A.interfacecode, 
            A.lipcode, 
            A.lipscale, 
            (A.lipscale * B.hsper/100)  AS 
            LipVal, 
            A.centercode, 
            A.centerscale, 
            (A.centerscale * B.centerper/100) AS 
            CenterVal, 
            A.stencilno 
        FROM #productiondetails A 
          INNER JOIN #blendmaster B 
            ON A.interfacecode = b.category 
             AND A.basecode = B.base 
             AND A.centercode = B.center ) 
        ---WHERE B.status = 'yes' this condtiton is not present in yout 2 tables) 
     ResTable 
       ON P.stencilno = ResTable.stencilno