UPDATE with CASE in MySQL

The CASE expression checks each condition and, if the first one is true, returns a value. It will therefore stop reading and return the outcome if a condition is true. It returns the value in the ELSE clause if no conditions are met. It returns NULL if the ELSE clause is absent and none of the requirements are met.

Simply put, a CASE expression is one that returns one of multiple possible result expressions after evaluating a series of conditions.

You will see how to use a CASE expression in the UPDATE statement in this example. You may also learn how to UPDATE the statements depending on IF ELSE types of search criteria from the example below. Additionally, you’ll learn how to bulk UPDATE MySQL data.

CREATE TABLE Dept(
DeptID INT,
DeptName VARCHAR(100));

INSERT INTO Dept VALUES
(10, ‘Sales Team’),
(20, ‘Advertising’),
(30, ‘Development’);

UPDATE Dept
SET DeptName = (CASE DeptID WHEN 10 THEN ‘Sales’
WHEN 20 THEN ‘Marketing’
WHEN 30 THEN ‘Software’
END)
WHERE DeptID IN(10,20,30);

SELECT * FROM Dept;

Here is the output of the above statement.

UPDATEwithCASE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s