Need to update the cost field by 3% for a number of fields

I need to update the cost and actual cost field by 3% for a number of products.

These are some of the products that needs to beupdated:


Select TypePrefix,Cost,ActualCost
From Setup_Tissue_Prefix
Where TypePrefix In ('0527','0527','1149','0023')
SanjeetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<air code>
UPDATE Setup_Tissue_Prefix
SET Cost = Cost * 1.03, ActualCost = ActualCost * 1.03
Where TypePrefix In ('0527','0527','1149','0023') 

Open in new window

0
Gerwin Jansen, EE MVETopic Advisor Commented:
To check before you do the update:

Select TypePrefix, Cost, Cost*1.03 CostNew, ActualCost, ActualCost*1.03 ActualCostNew
From Setup_Tissue_Prefix
Where TypePrefix In ('0527','0527','1149','0023');

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw small typo, lose the comma after the last value in the IN ( ).
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Gerwin Jansen, EE MVETopic Advisor Commented:
:) I just copied that (I'll remove it ...)
0
SanjeetAuthor Commented:
I also need to round to the nearest whole number. I already ran the script.

Do I just use the round function?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Round does the rounding, but still shows .0000's after the rounding based on the data types.
Just to be sure..
UPDATE Setup_Tissue_Prefix
SET Cost = CAST(ROUND(Cost * 1.03,0) as int), ActualCost = CAST(ROUND(ActualCost * 1.03,0) as int) 
Where TypePrefix In ('0527','0527','1149','0023') 

Open in new window


Disclaimer:  There might  be a better SQL 2012 answer, but I'm knee-deep in studying for the 2008 development exam, so that's what I have in my head right now.
0
Gerwin Jansen, EE MVETopic Advisor Commented:
Note: if you already ran the script then the last comment of Jim Horn is multiplying again by a factor of 1.03

Try to see what you would get:

select CAST(ROUND(Cost,0) as int),  CAST(ROUND(ActualCost,0) as int)
from Setup_Tissue_Prefix
Where TypePrefix In ('0527','0527','1149','0023') ;

If it looks OK, then do the update:

UPDATE Setup_Tissue_Prefix
SET Cost = CAST(ROUND(Cost,0) as int), ActualCost = CAST(ROUND(ActualCost,0) as int)
Where TypePrefix In ('0527','0527','1149','0023') ;
0
SanjeetAuthor Commented:
Ok I need to run in production now how would I re-write the scipt  by increasing price by 3% and then round to the nearest whole number?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If the only difference between comments already given and the last one is the price column, then just add price to the query:
UPDATE Setup_Tissue_Prefix
SET Price= CAST(ROUND(Price,0) as int)
Where TypePrefix In ('0527','0527','1149','0023') ;

Open in new window

0
SanjeetAuthor Commented:
I have not applied the price increase in production yet or the rounding and I need to do both.

What would the SQL be for that?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please explain to us in great detail how you believe the UPDATE statements provided you so far in this thread do not do exactly that.
0
SanjeetAuthor Commented:
This appears and please correct me to only round up to the nearest whole number and not increase the price by 3%

I would like to increase by 3% and round to the whole # in one sql:

UPDATE Setup_Tissue_Prefix
SET Price= CAST(ROUND(Price,0) as int)
Where TypePrefix In ('0527','0527','1149','0023') ;
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ah.  Going back to the comment before that with T-SQL:

UPDATE Setup_Tissue_Prefix
SET 
   Price = CAST(ROUND(Price* 1.03,0) as int), 
   Cost = CAST(ROUND(Cost * 1.03,0) as int), 
   ActualCost = CAST(ROUND(ActualCost * 1.03,0) as int) 
Where TypePrefix In ('0527','0527','1149','0023') 

Open in new window


If you no longer need to perform this action on Cost and ActualCost, just delete those two lines, and the comma at the end of the Price line.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SanjeetAuthor Commented:
ok thanks will give that a shot thanks sir!
0
SanjeetAuthor Commented:
STill testing
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.