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

Ivar
– Ivar

2015-12-03 12:20:44 +00:00
Commented Dec 3, 2015 at 12:20 — Ivar
– Ivar, Commented Dec 3, 2015 at 12:20

Gordon Linoff · Accepted Answer · 2015-12-03 12:23:05Z

1

You would use case for this purpose:

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

answered Dec 3, 2015 at 12:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhishek Sharma · Accepted Answer · 2015-12-03 12:23:22Z

0

use CASE on update

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

        WHEN designation = 'employees' THEN
                '2000'
        END

answered Dec 3, 2015 at 12:23

Abhishek Sharma

6,6241 gold badge17 silver badges20 bronze badges

Comments

Theo Babilon · Accepted Answer · 2015-12-03 12:23:38Z

0

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

answered Dec 3, 2015 at 12:23

Theo Babilon

6616 silver badges20 bronze badges

Comments

Muhammad Sohail Arif · Accepted Answer · 2015-12-03 12:25:22Z

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 3, 2015 at 12:25

Muhammad Sohail Arif

1582 silver badges15 bronze badges

5 Comments

Ivar Over a year ago

IF(designation='', 1200), thats the case when it is empty. The OP wants 1200 in any other case.

Muhammad Sohail Arif Over a year ago

Aah, I see... so what would go in the place of ' '?

Ivar Over a year ago

I'd say designation<>'manager', though i'm not sure if it works like this. (With 2x DA =)

Muhammad Sohail Arif Over a year ago

We could throw in an "else" some where I pressume?

Ivar Over a year ago

That would make more sence indeed.

Collectives™ on Stack Overflow

Update multiple fields on MySQL DB in a single query

4 Answers 4

Comments

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related