MySQL update multiple rows in one query

In this exercise, we will learn to update multiple rows with different values in one query. Suppose we have the following employee records and we want to update the phone number of some employees -

CREATE TABLE IF NOT EXISTS `empdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(25) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `empdata` (`id`, `name`, `email`, `phone`) VALUES
(1, 'Anjali', This email address is being protected from spambots. You need JavaScript enabled to view it.', 878433948),
(2, 'Priska', This email address is being protected from spambots. You need JavaScript enabled to view it.', 493905490),
(3, 'Abhi', This email address is being protected from spambots. You need JavaScript enabled to view it.', 403022139),
(4, 'Joya', This email address is being protected from spambots. You need JavaScript enabled to view it.', 342345329),
(5, 'Ammy', This email address is being protected from spambots. You need JavaScript enabled to view it.', 239848342),
(6, 'Lussi', This email address is being protected from spambots. You need JavaScript enabled to view it.', 490290331);

The best way to update multiple rows in just one statement is use CASE WHEN ELSE statement. In this, the statement will update the matched case and end otherwise, like-

UPDATE 'table_name' 
SET 'field_name' = CASE 'id'
WHEN '1' THEN 'value 1'
WHEN '2' THEN 'value 2'
WHEN '3' THEN 'value 3'
ELSE 'field_name'
END

Here, 'table_name' is the MySQL table name, 'field_name' is the column name.

The update query to update the phone number of some employees are as follows -

UPDATE empdata SET phone = CASE 
WHEN id=1 THEN '840932345'
WHEN id=2 THEN '890040908'
WHEN id=3 THEN '675748389'
WHEN id=6 THEN '909030309'
ELSE phone
END;

let's check the updated data -

SELECT * FROM 'empdata';

+----+--------+--------------------+-----------+
| id | name   | email              | phone     |
+----+--------+--------------------+-----------+
|  6 | Lussi  | This email address is being protected from spambots. You need JavaScript enabled to view it.  | 909030309 |
|  4 | Joya   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 342345329 |
|  5 | Ammy   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 239848342 |
|  2 | Priska | This email address is being protected from spambots. You need JavaScript enabled to view it. | 890040908 |
|  3 | Abhi   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 675748389 |
|  1 | Anjali | This email address is being protected from spambots. You need JavaScript enabled to view it. | 840932345 |
+----+--------+--------------------+-----------+