Learn how to a build a cloud-first strategyRegister Now

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

Sql Update with Calculation

I need to do an update statement where 3 columns are multiplied together and stored into another column.  I'm not quite sure how to accomplish this.

 
UPDATE tblRequestHistory SET strPrintCost = (SUM(CONVERT(numeric(8, 4), strPrintCost) * intPages * intCopies)) WHERE strPrintCost IS NOT NULL

Open in new window


intPages and intCopies are stored in the tblRequestHistory.  strPrintCost is also in the table and has a cost already assigned to it.  I need to multiply these 3 fields together and overwrite the sum to strPrintCost.  
0
gfedz
Asked:
gfedz
  • 2
2 Solutions
 
dirknibleckCommented:
What is the purpose of the SUM in your formula? Would you not be accomplishing the same thing if you removed it? Your query looks good, with the exception of the SUM().
0
 
Ephraim WangoyaCommented:


UPDATE tblRequestHistory
SET strPrintCost = cast(CONVERT(numeric(8, 4), strPrintCost) * intPages * intCopies as varchar)
WHERE strPrintCost IS NOT NULL
0
 
Ephraim WangoyaCommented:

The result column should better be a numeric field instead of doing the conversion to string

UPDATE tblRequestHistory
SET strPrintCost = CONVERT(numeric(8, 4), strPrintCost) * intPages * intCopies
WHERE strPrintCost IS NOT NULL
0
 
gfedzAuthor Commented:
I'm still pretty new to syntax in sql.  I assumed it was something like this.  Thank you guys for your time.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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