CODEWITHSUNDEEP
sql-serverms-accessms-access-2013ms-access-2003
Bala
Bala

Reputation: 159

How to write update query using Inner join

I have this query 

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

I need to update ProductionDetails table on BaseValue, TreadValue, CenterValue, LipValue Fields with the calculated value in (SELECT Query ... ) AS ResTable .

if i write command to select from ResTable i get value, but if i update it says error "Operation must use an updateable query". i need to run this on MS Access 2013

Here is the query for sample data 

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)

Upvotes: 1

Views: 94

Answers (1)

Chanukya
Chanukya

Reputation: 5893

i think update should be done like this

  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

Upvotes: 2

Related Questions