SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Increase values with update statement


Increase values with update statement

Author
Message
ByronOne
ByronOne
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 457
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26500 Visits: 17557
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.

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)
ByronOne
ByronOne
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 457
Cheers Sean for your quick response on this...

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

Thanks again.
ByronOne
ByronOne
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 457
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
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26500 Visits: 17557
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.

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)
ByronOne
ByronOne
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 457
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
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26500 Visits: 17557
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. :-P

_______________________________________________________________

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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search