Q:

MySQL update multiple rows in one query

belongs to collection: MySQL Exercises

YasminRadwan26
Save to bookmark
|
2022-05-14
|
  253 
0

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', '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,
login and add your own answer to this question

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 Exercises
This question belongs to these collections

Similar questions

need a help?

find thousands of online teachers now
MySQL get nth highest paid and nth lowest paid sal... >>
<< Display the alternate rows from MySQL table...