Update multiple fields on MySQL DB in a single query

Question

Here is the structure of my database named employee

mysql> desc employee;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| emp_code    | int(11)       | NO   | PRI | NULL    |       |
| emp_name    | varchar(25)   | YES  |     | NULL    |       |
| designation | varchar(25)   | YES  |     | NULL    |       |
| department  | varchar(25)   | YES  |     | NULL    |       |
| basic       | decimal(10,2) | YES  |     | NULL    |       |
| DA          | decimal(10,2) | YES  |     | NULL    |       |
| gross_pay   | decimal(10,2) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

My question is, how can I update DA with value 1500 for rows with designation manager and update DA with value 2000 for all other employees.

Possible duplicate of [MySQL update case help](http://stackoverflow.com/questions/6734231/mysql-update-case-help) — Ivar, Dec 03 '15 at 12:20

score 1 · Answer 1 · answered Dec 03 '15 at 12:23

1

You would use case for this purpose:

update employees
    set DA = (case when designation = 'manager' then 1500 else 2000 end);

answered Dec 03 '15 at 12:23

Gordon Linoff

1,242,037
58
646
786

score 0 · Accepted Answer · answered Dec 03 '15 at 12:23

0

use CASE on update

UPDATE  employee
SET     DA =
        CASE
        WHEN designation = 'manager' THEN
                '1500'

        WHEN designation = 'employees' THEN
                '2000'
        END

answered Dec 03 '15 at 12:23

Abhishek Sharma

6,689
1
14
20

score 0 · Answer 3 · answered Dec 03 '15 at 12:23

0

UPDATE employee
SET DA = CASE 
WHEN designation = "manager" THEN 1500
ELSE 2000
END

answered Dec 03 '15 at 12:23

Theo Babilon

661
6
18

score 0 · Answer 4 · answered Dec 03 '15 at 12:25

0

UPDATE employee SET
DA = IF(designation='manager', 1500),
DA = IF(designation='', 1200)
WHERE designation IN('manager', '');

Did some research and constructed the above. Try it out, see what happens.

answered Dec 03 '15 at 12:25

Muhammad Sohail Arif

158
2
15

`IF(designation='', 1200)`, thats the case when it is empty. The OP wants 1200 in **any** other case. – Ivar Dec 03 '15 at 12:26
Aah, I see... so what would go in the place of ' '? – Muhammad Sohail Arif Dec 03 '15 at 12:27
I'd say `designation<>'manager'`, though i'm not sure if it works like this. (With 2x `DA =`) – Ivar Dec 03 '15 at 12:28
We could throw in an "else" some where I pressume? – Muhammad Sohail Arif Dec 03 '15 at 12:31
That would make more sence indeed. – Ivar Dec 03 '15 at 12:40

Update multiple fields on MySQL DB in a single query

4 Answers4