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
GO
CREATE 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
GO
CREATE 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.

UPDATE emp
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