SQL Server: Use a CASE Expression in an Update Statement

The CASE expression is used to compare one expression with a set of expressions in SQL. The result of the CASE expression is a Boolean value, true or false. We can use various DML statements like INSERT, SELECT, DELETE and UPDATE with a CASE statement. In this Tech-Recipes tutorial, we will see how to use a CASE expression with UPDATE statements.

We can begin by walking through an example.

Start by creating a table and naming it “employee.”

if exists ( SELECT name from sys.tables where name ='employee')
drop table employee
    empid INT,
    ename VARCHAR(20),
    sal   INT

Insert some sample data in the employee table.

insert into employee values (100, 'jon smith', 50000);
insert into employee values (101, 'mike', 2000);
insert into employee values (102, 'ab luther', 70000);
insert into employee values (103, 'vish dalvi', 60000);
select * from employee;

empid       ename                sal
----------- -------------------- -----------
100         jon smith            50000
101         mike                 2000
102         ab luther            70000
103         vish dalvi           60000

We have inserted four rows and all rows are NON NULL. Thus, all rows have a valid value.

Now let us create another table with empid and sal columns as listed in employee table.

if exists ( SELECT name from sys.tables where name ='emp')
drop table emp
     empid INT,
     sal   INT

Now, insert some sample data.

insert into emp values (100, 50000);
insert into emp values (101, NULL);
insert into emp values (102, NULL);
insert into emp values (103, NULL);
select * from emp;

empid       sal
----------- -----------
100         50000
101         NULL
102         NULL
103         NULL


Please note that we have inserted the same empid from the employee table in the emp table, but the sal column is NULL for three employees.

Now, we want to update table emp and set the sal column value equal to the sal column in the employee table. In the following query, we need to use a CASE expression with the update statement.

SET    sal = ( CASE
                 WHEN e2.sal IS NULL THEN e1.sal
                 ELSE e2.sal
               END )
FROM   employee e1 INNER JOIN emp e2
ON     e1.empid = e2.empid;

In the query above, we are checking whether or not the e2.sal column in the emp table is NULL. If it is NULL, then update the value with the sal column of the employee table or else keep it as it is in else condition.

Now, after updating, if you query the emp table, you can see the value for the sal column in the emp table. This has NULL values updated with employee table sal column on the basis of matching empid from both the tables using inner join.

select * from emp;

empid       sal
----------- -----------
100         50000
101         2000
102         70000
103         60000


SQL Server: Using Case expression in an Update Statement


About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply