Updating multiple columns based on multiple conditions

Question

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

Nothing in your table is called score. I don't know what represents morning and afternoon sessions. Please clarify your question. — Gordon Linoff
– Gordon Linoff, Commented Feb 7, 2019 at 17:12

donPablo · Accepted Answer · 2019-02-07 19:47:27Z

1

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

answered Feb 7, 2019 at 19:47

donPablo

1,9691 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

donPablo Over a year ago

@Lohengrin Does this solve your problem? If so, please click on the check mark

Collectives™ on Stack Overflow

Updating multiple columns based on multiple conditions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related