Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Increase values with update statement Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 10:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:15 AM
Points: 145, Visits: 373
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
Post #1435011
Posted Monday, March 25, 2013 10:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1435013
Posted Monday, March 25, 2013 10:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:15 AM
Points: 145, Visits: 373
Cheers Sean for your quick response on this...

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

Thanks again.
Post #1435019
Posted Tuesday, March 26, 2013 8:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:15 AM
Points: 145, Visits: 373
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
Post #1435502
Posted Tuesday, March 26, 2013 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1435507
Posted Tuesday, March 26, 2013 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:15 AM
Points: 145, Visits: 373
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...
Post #1435545
Posted Tuesday, March 26, 2013 8:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1435556
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse