Update more than one column with single Case When logic - SQL Server

Question

I need to create a following XML with Custom Element with attribute using Hard-code data.

Table schema: StudentMark:

CREATE TABLE [dbo].[StudentMark]
(
    [StudentMarkId] int IDENTITY(1,1) NOT NULL,
    [StudentId] uniqueidentifier NOT NULL,
    [SubjectId] uniqueidentifier NOT NULL,
    [Score] int NOT NULL,
    [GeneratedOn] datetime2(2) NOT NULL,
    [IsPass] bit NULL,
    [Result] varchar(100) NULL,
    CONSTRAINT [PK_StudentMark] 
       PRIMARY KEY CLUSTERED ([StudentMarkId] ASC)
) ON [PRIMARY]

Sample seed data

INSERT INTO [dbo].[StudentMark] ([StudentId], [SubjectId], [GeneratedOn], [Score])
VALUES ('FC3CB475-B480-4129-9190-6DE880E2D581', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '2017-08-10 10:10:15', 95),
       ('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '2017-08-10 10:10:15', 60),
       ('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '2017-08-16 09:06:20', 25),
       ('FC3CB475-B480-4129-9190-6DE880E2D581', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '2017-08-16 09:06:20', 45);

Requirement: I need to update couple of column [IsPass] and [Result] based on single logic.

Query #1:

UPDATE SM
SET SM.[Result] = CASE WHEN SM.[Score] >= 75 THEN N'OUTSTANDING'
     WHEN SM.[Score] >= 60 AND [Score] < 75 THEN N'VERY GOOD'
     WHEN SM.[Score] >= 50 AND [Score] < 60 THEN N'GOOD'
     WHEN SM.[Score] >= 40 AND [Score] < 50 THEN N'AVERAGE'
     ELSE N'FAIL' END
FROM [dbo].[StudentMark] SM

Query #2:

UPDATE SM
SET SM.[IsPass] = CASE WHEN SM.[Result] = N'FAIL' THEN 0 ELSE 1 END
FROM [dbo].[StudentMark] SM

How could I merge these two query in a single UPDATE Query without replicating the CASE WHEN.

Kindly assist me.

Usually storing same information several times (score, score as text, pass / fail) is a bad idea since if you update the data, something might be missed at that point and then there's a mismatches in data. Having just score + logic elsewhere (Application layer, view, computed column) might be worth considering — James Z, Aug 23 '17 at 14:38

score 1 · Accepted Answer · answered Aug 23 '17 at 12:56

Something like this:

WITH DataSource ([StudentMarkId], [Result]) AS
(
    SELECT [StudentMarkId]
          ,CASE WHEN SM.[Score] >= 75 THEN N'OUTSTANDING'
             WHEN SM.[Score] >= 60 AND [Score] < 75 THEN N'VERY GOOD'
             WHEN SM.[Score] >= 50 AND [Score] < 60 THEN N'GOOD'
             WHEN SM.[Score] >= 40 AND [Score] < 50 THEN N'AVERAGE'
            ELSE N'FAIL' END
    FROM [dbo].[StudentMark]
)
UPDATE [dbo].[StudentMark]
SET SM.[Result] = DS.[Result]
   ,SM.[IsPass] = CASE WHEN DS.[Result] = N'FAIL' THEN 0 ELSE 1 END
FROM [dbo].[StudentMark] SM
INNER JOIN DataSource DS
    ON SM.[StudentMarkId] = DS.[StudentMarkId];

score 1 · Answer 2 · answered Aug 23 '17 at 12:59

You could simply repeat the logic:

UPDATE SM
    SET SM.[Result] = (CASE WHEN SM.[Score] >= 75 THEN N'OUTSTANDING'
                            WHEN SM.[Score] >= 60 THEN N'VERY GOOD'
                            WHEN SM.[Score] >= 50 THEN N'GOOD'
                            WHEN SM.[Score] >= 40 THEN N'AVERAGE'
                            ELSE N'FAIL'
                       END),
        SM.IsPass = (CASE WHEN SM.Score >= 40 THEN 1 ELSE 0 END)
    FROM [dbo].[StudentMark] SM;

Note: CASE expressions are guaranteed to be evaluated in order, so you only need once comparison per row.

I would point out, though, that a better alternative is probably to dispense with IsPass as a column in the table and to make it a computed column:

alter table [dbo].[StudentMark]
    add IsPass as (CASE WHEN Result <> N'FAIL' THEN 1 ELSE 0 END);

This ensures that the value is always correct. In fact, you can do the same with Result, so both are computed.

Just code · Answer 3 · 2017-08-23T13:01:37.430

0

UPDATE SM
SET SM.[Result] = CASE WHEN SM.[Score] >= 75 THEN N'OUTSTANDING'
     WHEN SM.[Score] >= 60 AND [Score] < 75 THEN N'VERY GOOD'
     WHEN SM.[Score] >= 50 AND [Score] < 60 THEN N'GOOD'
     WHEN SM.[Score] >= 40 AND [Score] < 50 THEN N'AVERAGE'
     ELSE N'FAIL' END, SM.[IsPass] = CASE WHEN SM.[Score] < 40 THEN 0 ELSE 1 END
FROM [dbo].[StudentMark] SM

You can easily do this by adding ,(comma)

edited Aug 23 '17 at 13:01

answered Aug 23 '17 at 12:54

Just code

13,553
10
51
93

I already tried this logic but It always update `[IsPass]` to `1`. That the reason I posted this question. – B.Balamanigandan Aug 23 '17 at 12:57
OH!!, You initially you are trying two transactions in single query. You need to use two query. or you can change a logic little bit like I did in my edited answer. – Just code Aug 23 '17 at 13:01

Update more than one column with single Case When logic - SQL Server

3 Answers3