How Can I Compute Values In A Tsql Update Statement?

I want to create three temp tables and then combine their contents like so: CREATE TABLE #TEMP1 MEMBERITEMCODE VARCHAR(25), WEEK1USAGE VARCHAR(25), WEEK1PRICE VARCHAR(25); INSERT

Solution 1:

First thing: why are you using varchar datatype for following columns?

USAGEVARIANCE, ,WEEK2USAGE,WEEK1USAGE,PRICEVARIANCE , WEEK2PRICE , WEEK1PRICE, PRICEVARIANCEPERCENTAGE, WEEK2PRICE , WEEK1PRICE,WEEK1PRICE ;

It is better to use decimal or integer data types. As you are using sum() function to calculate its value and it will return int/decimal value

Second: you can calculate USAGEVARIANCE,PRICEVARIANCE,PRICEVARIANCEPERCENTAGE while inserting into the table. For example:

select a,b, (a-b) as USAGEVARIANCE, (c-d) as PRICEVARIANCE fromtable

etc and then insert it into the temporary table

Solution 2:

Using computed columns:

CREATETABLE #TEMPCOMBINED(
  MEMBERITEMCODE VARCHAR(25),
  DESCRIPTION VARCHAR(200),
  THIS VARCHAR(25),
  THAT VARCHAR(25),
  THEOTHERTHING VARCHAR(25),
  WEEK1USAGE DECIMAL(18,10),
  WEEK1PRICE DECIMAL(18,10),
  WEEK2USAGE DECIMAL(18,10),
  WEEK2PRICE DECIMAL(18,10),
  USAGEVARIANCE   AS WEEK2USAGE - WEEK1USAGE,
  PRICEVARIANCE   AS WEEK2PRICE - WEEK1PRICE,
  PRICEVARIANCEPERCENTAGE  AS (WEEK2PRICE - WEEK1PRICE) / WEEK1PRICE
);

LiveDemo

And skip them in INSERT statement:

INSERTINTO #TEMPCOMBINED (MEMBERITEMCODE, DESCRIPTION, THIS, THAT, THEOTHERTHING, WEEK1USAGE, WEEK1PRICE, WEEK2USAGE, WEEK2PRICE)
SELECT T1.MEMBERITEMCODE, T3.DESCRIPTION, T3.THIS, T3.THAT, T3.THEOTHERTHING, T1.WEEK1USAGE, T1.WEEK1PRICE, T2.WEEK2USAGE, T2.WEEK2PRICE
FROM #TEMP1 T1
LEFTJOIN #TEMP2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE
LEFTJOIN #TEMP3 T3 ON T1.MEMBERITEMCODE = T3.MEMBERITEMCODE

Warning:

Division by WEEK1PRICE so you could add (WEEK2PRICE - WEEK1PRICE) / NULLIF(WEEK1PRICE,0) to avoid Math exception or add CHECK constraint to ensure that WEEK1PRICE is not 0.

Solution 3:

Update column in one table you can use Cross join on simple join

For example:

Update m
Set col1 = col1 - col2
From mytable m 
join mytable n On m.col2 = n.col2