Increase values with update statement

Question

Increase values with update statement

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
March 25, 2013 at 10:18 am

#291564

Hi everyone
I hope somebody can help...
I have a table in SQL with the following columns: FinancialYear (varchar), Site (varchar), ProductRange (varchar), ProductName (varchar), PlannedSales (decimal).
I want to increase the PlannedSales in the table for each of the sites but by a different amount. For the North Site I want to increase PlannedSales by 0.05% and for the South Site by 0.19%. All the other sites should keep their current PlannedSales totals. The increase will need to be spread across all product ranges and actual products ie all products should increase their PlannedSales by 0.05% in the North Site.
Thanks in advance.
BO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

ByronOne (3/25/2013)
Hi everyone
I hope somebody can help...
I have a table in SQL with the following columns: FinancialYear (varchar), Site (varchar), ProductRange (varchar), ProductName (varchar), PlannedSales (decimal).
I want to increase the PlannedSales in the table for each of the sites but by a different amount. For the North Site I want to increase PlannedSales by 0.05% and for the South Site by 0.19%. All the other sites should keep their current PlannedSales totals. The increase will need to be spread across all product ranges and actual products ie all products should increase their PlannedSales by 0.05% in the North Site.
Thanks in advance.
BO

All you need here is an update with a case expression.

update table

set PlannedSales =

case Site

when 'North' then PlannedSales * 1.05

when 'South' then PlannedSales * 1.19

end

where Site in ('North', 'South')

This is of course untested because we don't have ddl or sample data to work with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 2

Cheers Sean for your quick response on this...

Looks good, I'll give it a try..

Thanks again.

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 3

One problem.

I want to sum the PlannedSales then multiply by the factor increase. However I'm getting an error which says I can't have an aggregate in an update statement.

Any ideas how to resolve this problem?

Thanks

BO

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

ByronOne (3/26/2013)
One problem.
I want to sum the PlannedSales then multiply by the factor increase. However I'm getting an error which says I can't have an aggregate in an update statement.
Any ideas how to resolve this problem?
Thanks
BO

The query I posted didn't have an aggregate. Why do you want an aggregate in your update? That just doesn't make any sense at all.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 5

Sorry I should have said, I adapted the code a little but actually as you've indicated it didn't make sense to do so!

I'm going with the original (correct and working) code you supplied..

Thanks again...

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

ByronOne (3/26/2013)
Sorry I should have said, I adapted the code a little but actually as you've indicated it didn't make sense to do so!
I'm going with the original (correct and working) code you supplied..
Thanks again...

You're welcome. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/