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.  
LVL 1
gfedzAsked:
Who is Participating?
 
dirknibleckConnect With a Mentor Commented:
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 WangoyaConnect With a Mentor Commented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.