Update same row based on other column

Question

I need the sql for the following scenario.

I am having some set of selected rows.

For example, Field table, I've filtered the following values

Id | yearId
1  | 1 
1  | 2 
1  | 3 
1  | 4 
1  | 5

After filtered, i am doing inner join with the existing table,

Consider Field table having the following columns

Id, yearid, value1, value2, value3, value4,NextYearValue,PreviousYearValue

I need to do inner join with the table and should update like following,

value2 = value2 + value1
value3 = value2 + value3
value4 = value3 + value2
NextYearValue = Value2 + value3 + value4
PreviousYearValue = NextYearValue (should be taken from the previous year value)

value3 should get the updated value2 after calculate value2+value1

value4 should get updated value3 and value2 after finish calculation NextYearValue should get all the udpated value.

PreviousYearValue should get the update value from the previous year value (that means previous row in this scenario I think)

Answer 1

Either set the field the same way as you did with the first value or perform 2 updates.

Update @Tbl
set value1=value1+value1,
value2=value1+value1

This is basically what you want to do right?

If for some reason you cant do that you could do this:

Update @Tbl
set value1=value1+value1

Update @Tbl
set value2=value1

Though thats 2 calls so that isn't what i would do

If for some reason this isn't satisfactory maybe you could elaborate on your case? Cause it is kind of strange to have 2 columns with identical values.

Answer 2

I came up with two approaches, I don't know which is faster.

Since your calculations are not complex, you can just do the math:

UPDATE FieldTable SET 
value2 = value1 + value2
value3 = value1 + 2*value2 + value3
value4 = 2*value1 + 4*value2 + 2*value3
NextYearValue = 4*value1 + 7*value2 + 3*value3
PreviousYearValue = NextYearValue 

On a MySql approach, you can just use variables:

UPDATE FieldTable 
JOIN 
(
  SELECT 
  id,
  @uvalue2 :=value2   + value3   uvalue2,
  @uvalue3 :=@uvalue2 + value3   uvalue3,
  @uvalue4 :=@uvalue2 + @uvalue3 uvalue4,
  @uvalue2 + @uvalue3 + @uvalue4 uNextYearValue,
  NextYearValue uPreviousYearValue
  FROM FieldTable 
) as uFieldTable 
ON FieldTable.id = uFieldTable.id
SET 
value2 = uvalue2,
value3 = uvalue3,
value4 = uvalue4,
NextYearValue = uNextYearValue,
PreviousYearValue = uPreviousYearValue;

Considering of course that you have unique ids.

On Sql Server, things get more complex, since you probably have to use Common Table Expressions

WITH FieldTable2 AS(
SELECT id, value2 + value3 value2, value3, value4, NextYearValue FROM FieldTable
) ,FieldTable3 AS(
SELECT id, value2, value3 + value2 value3, value4, NextYearValue FROM FieldTable2
) ,FieldTable4 AS(
SELECT id, value2, value3, value3 + value2 value4, NextYearValue FROM FieldTable3
) ,FieldTable5 AS(
SELECT id, value2, value3, value4, value2 + value3 + value4 NextYearValue FROM FieldTable4
) 
UPDATE FieldTable 
SET 
value2 = FieldTable5.value2,
value3 = FieldTable5.value3,
value4 = FieldTable5.value4,
NextYearValue = FieldTable5.NextYearValue
FROM 
FieldTable5
WHERE FieldTable.id = FieldTable5.id

Answer 3

The right hand side of the assignments in an update statement are evaluated before being modified by any other assignments in the the same statement. Just apply the same calculation on both expressions and you should be OK:

UPDATE @Tbl
SET    value1 = value1 + value1,
       value2 = value1 + value1

Answer 4

 Update @Tbl
  set value1=value1+value1 
 update @Tbl set value2=value1