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

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

Table schema: StudentMark:

  • SQL Server Multiple Joins Are Taxing The CPU
  • SQL Server (localdb)\v11.0 explained
  • How do I perform automated unit testing in SSIS packages?
  • Collation conflict SQL Server 2008
  • Converting Columns into rows with their respective data in sql server
  • Bulk insert from CSV file - skip duplicates
  • 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.

    3 Solutions collect form web for “Update more than one column with single Case When logic – SQL Server”

    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];
    

    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.

    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)

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.