Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Update query

I have the folllowing table named TechList
with the following sample data

TotLaborPartsAll    TotLaborPartsTA   TotLaborPartsXTR  PartsLabor
3324.73                   319.92                 1                              0  
400                          500                       2                             0



I am trying to write a query that will update partsLabor with the following

If TotLaborPartsALL > TotLaborPArtsTA +  TotLaborPartsXTR then totLaborPartsall - (totlaborPartsta - totlaborpartsxtr) else 0

Using the sample data above

 partsLabor in the first row should be set to 3003.81
(3324.73 - (319.92 + 1))

partsLabor in the second row should be set to 0 since TotLaborPartsALL is not greater than TotLaborPArtsTA +  TotLaborPartsXTR



0
johnnyg123
Asked:
johnnyg123
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Alex MatzingerDatabase AdministratorCommented:
Update table TechList set partsLabor=
     CASE
          WHEN (TotLaborPartsALL > TotLaborPArtsTA +  TotLaborPartsXTR)
                    THEN totLaborPartsall - (totlaborPartsta - totlaborpartsxtr)
          ELSE 0
      END
0
 
Rey Obrero (Capricorn1)Commented:
test this

update TechList
set PartsLabor=iif([TotLaborPartsALL] > (nz([TotLaborPArtsTA] +  nz([TotLaborPartsXTR])), [TotLaborPartsALL]-(nz([TotLaborPArtsTA] - nz([TotLaborPartsXTR])),0)
0
 
Alex MatzingerDatabase AdministratorCommented:
sorry, the first line should be

Update TechList set partsLabor =
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HainKurtSr. System AnalystCommented:
Update table TechList
set
partsLabor=IIf(TotLaborPartsALL > TotLaborPArtsTA +  TotLaborPartsXTR, totLaborPartsall - (totlaborPartsta - totlaborpartsxtr),0)

assuming all have numeric values, if some may have null values, use NZ(col, 0) for all columns that may have null values like

Update table TechList
set
partsLabor=IIf(NZ(TotLaborPartsALL,0) > NZ(TotLaborPArtsTA,0) + NZ(TotLaborPartsXTR,0), NZ(totLaborPartsall,0) - (NZ(totlaborPartsta,0) - NZ(totlaborpartsxtr,0)),0)
0
 
johnnyg123Author Commented:
got the following to work


UPDATE TechList SET PartsLabor = IIf(nz(TotLaborPartsALL,0)>nz(TotLaborPArtsTA,0)+nz(TotLaborPartsXTR,0),nz(totLaborPartsall,0)-(nz(totlaborPartsta,0)+nz(totlaborpartsxtr,0)),0);
0
 
HainKurtSr. System AnalystCommented:
the one worked is the one I posted but you did not give me any points :(
0
 
Rey Obrero (Capricorn1)Commented:
HainKurt,
sorry but, the query you posted will give you an error.
0
 
HainKurtSr. System AnalystCommented:
:) I forgot to remove keyword "table" from the query :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now