Update table Set with calculations and varibles

Question

Post reply

Update table Set with calculations and varibles

Ken at work

Hall of Fame

Points: 3426
More actions
September 22, 2010 at 7:29 am

#98904

I know I can do this easily enough
Update tbl_UL
Set WR = vtbl_Combined.BW
From
(
Select
Blah
, Blah
From
A bunch of tables and joins
) As vtbl_Combined
I can even WR = vtbl_Combined.BW * 2
or whatever.
However the calculation is actually quite complex and requires several If, variables, etc...
In an over simplified example can I do something like this???
Declare @fE_Rate Real;
Declare @fW_Rate Real;
Update tbl_UL
Set WR =
(
Set @fE_Rate = vtbl_Combined.ERate
Set @fW_Rate = vtbl_Combined.BRate
If @fE_Rate > @fW_Rate
Begin Select @fE_Rate End;
Else
Begin Select @fW_Rate End;
)
From
(
Select
Blah
, Blah
From
A bunch of tables and joins
) As vtbl_Combined
I realize that this example is simplistic and might be accomplished with a Case When etc... but like I said the final calculation has many variables. Some will be added based on another variable and all will be compaired to yet another to choose the greater. So.....
In short, I am stuck and could use a little insight. If I can use syntax that will allow variables and calculations within an Update please let me know. Point me in the right direction.
I know I say this alot. But I really do appreciate everyone's help,

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You have to use CASE in a select or update statement. I think what you want is something like this:

Update

tbl_UL

Set WR = vtbl_Combined.BW

From

(Select

CASE

WHEN t.Erate > T2.brate THEN T.erate

ELSE t2.brate

END AS bw

From

table1 T JOIN

table2 T2

ON T.pk = T2.pk) As vtbl_Combined

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ken at work Hall of Fame Points: 3426 More actions · Answer 2

I don't believe Case will allow me to do what I need.

As I said there will be many (100 plus) variables which some may or may not be used depending on other fields in the record from the 'From'.

Is there a way to set variables, do the math, manipulate them, compare them and use that number.

Something like...

If I could do a Case When to a variable.

The another to that variable

Then another to that variable

and so on...

Finally using the result to Update the field.

That is what I need.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Well you can have as many WHEN statements in a CASE as needed and you CAN nest CASE statements. This can get ugly and hard to maintain so you may want to take a performance hit and set the variables ahead of time and use IF...ELSE blocks with multiple selects and then just do an UPDATE Table set column = @final_variable for maintainability.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Ken@Work (9/22/2010)
I don't believe Case will allow me to do what I need.
As I said there will be many (100 plus) variables which some may or may not be used depending on other fields in the record from the 'From'.
Is there a way to set variables, do the math, manipulate them, compare them and use that number.
Something like...
If I could do a Case When to a variable.
The another to that variable
Then another to that variable
and so on...
Finally using the result to Update the field.
That is what I need.

There are two ways to do this using SQL Server 7/2000.

You could use a cursor, which would be slow and complex. Or you could use the "Quirky Update", which could be several orders of magnitude faster. The Quirky Update is described here[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ken at work Hall of Fame Points: 3426 More actions · Answer 5

Fantastic.

I can make this work.

So, no "(" and use 'Case'

That was I can set variables, manipulate them and when I get to the result.

I didn't realize setting a variable was as easy as (the same as) setting a Column.

Thank you Quirky Update it is!