MySQL update multiple rows in one query

YasminRadwan26

Save to bookmark

2022-05-14

587

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', 'anjali@example.com', 878433948),
(2, 'Priska', 'priska@example.com', 493905490),
(3, 'Abhi', 'abhi@example.com', 403022139),
(4, 'Joya', 'joya@example.com', 342345329),
(5, 'Ammy', 'ammy@example.com', 239848342),
(6, 'Lussi', 'lussi@example.com', 490290331);

Sql programming,

All Answers

YasminRadwan26

2022-05-14

need an explanation for this answer? contact us directly to get an explanation for this answer

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  | lussi@example.com  | 909030309 |
|  4 | Joya   | joya@example.com   | 342345329 |
|  5 | Ammy   | ammy@example.com   | 239848342 |
|  2 | Priska | priska@example.com | 890040908 |
|  3 | Abhi   | abhi@example.com   | 675748389 |
|  1 | Anjali | anjali@example.com | 840932345 |
+----+--------+--------------------+-----------+

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

MySQL update multiple rows in one query

All Answers

This question belongs to these collections

Similar questions

need a help?

find thousands of online teachers now