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
Follow the reactions below and share your own thoughts.