UPDATE QUERY USING CASE STATEMENT IN SQL SERVER

One of the keys to SQL Server database performance if keeping your transactions as short as possible. In this article we will look at a couple of tricks using the CASE statement to perform multiple updates on a table in a single operation. By doing this, some transactions can be shorted, and performance boosted.

--First Create a Table
CREATE TABLE [dbo].[UsingCaseInUpdateQuery](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[gender] [varchar](50) NOT NULL,
[address] [varchar](100) NULL,)

--Insert Data in to the Table
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh','male','almora')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh1','male','almora')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh2','male','ranikhet')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh3','male','nainital')

insert into [UsingCaseInUpdateQuery] (name,gender,address) values('naina','female','almora')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('pooja','female','ranikhet')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('ritu','female','nainital')

--Once Check the data in the table
select * from [UsingCaseInUpdateQuery]
 

USING CASE STATEMENT IN A SQL UPDATE QUERY
--Now Update all the Male Gender with Female and Female with Male
Update Query Using Case for the fast execution of data
update [UsingCaseInUpdateQuery] set gender = case
when gender ='male' then 'female'
when gender = 'female' then 'male'
end

*Note that there is a definite “top-down” priority involved in the CASE statement.

Multiple Updates to a Single column:

If we code our update like the example below, then the table will only need to be read once. For large tables, this can save us a lot of disk I/O, especially if the query requires a table scan over a long table

Update [UsingCaseInUpdateQuery]
Set address =
Case
    When gender = 'female'
    Then 'Almora'
    When id = '5'
    Then 'Delhi'
End
Where id = '5' OR gender = 'female'

Continues…