B.Balamanigandan B.Balamanigandan - 1 month ago 5
SQL Question

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:

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.

Answer Source

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