CODEWITHSUNDEEP
sqldatabase
Lohengrin
Lohengrin

Reputation: 125

Updating multiple columns based on multiple conditions

I've below table with some results for both Morning and Afternoon session (for different periods). I would like to updated the results based on the simple condition:

Check if in 2 following morning sessions there was a change - if not add 5 to the score: Example: ID=1, Mor2=C, Mor3=C so Score_M3 = 5+5= 10 (new value). All updated values are marked in the 'Wanted' table. How can I write this in SQL? I will have a lot of columns and IDs.

My dataset:

    ID  Mor1 Aft1  Mor2  Aft2  Mor3  Aft3  Score_M1 Score_A1  Score_M2  Score_A2  Score_M3  Score_A3
    1   A    A     C     B     C     B       1        1          1         1         5        6
    2   C    C     C     B     C     B       1        1          1         1         4        5
    3   A    A     A     A     A     A       1        1          1         1         4          1

Wanted :

    ID  Mor1 Aft1  Mor2  Aft2  Mor3  Aft3  Score_M1 Score_A1   Score_M2  Score_A2   Score_M3   Score_A3
    1   A    A     C     B     C     B         1        1         1         1          *10        6
    2   C    C     C     B     C     B         1        1        *6         1          *9         5
    3   A    A     A     A     A     A         1        1        *6         1          *9         1

Upvotes: 0

Views: 69

Answers (1)

donPablo
donPablo

Reputation: 1969

Here is the SQL to get you started. You can add many more columns as you see fit.

Can we restate as SAME, rather than Change?
If Mor1 = Mor2 then add +5 to Score2
If Mor2 = Mor3 then add +5 to Score3

UPDATE [StackOver].[dbo].[UpdateMultiCols]
   SET 
       [Score_M1] = Score_M1 

      ,[Score_M2] = Score_M2 +
      Case When Mor1 = Mor2 Then 5 else 0 End 

      ,[Score_M3] = Score_M3 +
       Case When Mor2 = Mor3 Then 5 else 0 End 
GO

enter image description here

Upvotes: 1

Related Questions