# Update query

Hi,

I have a sql table where i'll store all my employee details. employees are of different categories like "A", "B", "C" and "D". now i want to a single update query which updates the salaries of employees. "A" and "B" category employee's salary has to be increased by 20%, for "C" it is 15% and for "D" it is 12%. i want to make all these in a single update statement. how can i do that.

Regards
Rajeeva
###### Who is Participating?

Commented:
Use a case statement.

UPDATE EMPLOYEE
SET SALARY = CASE
WHEN CATEGORY in ('A', 'B') THEN 1.2
WHEN CATEGORY = 'C' THEN 1.15
WHEN CATEGORY = 'D' THEN 1.12
END * SALARY
WHERE CATEGORY in ('A','B','C','D')
0

Commented:
Exec below query

UPDATE                              EMPLOYEE
SET                                     SALARY = CASE
WHEN CATEGORY = 'A' OR CATEGORY = 'B' THEN 1.2 * SALARY
WHEN CATEGORY = 'C' THEN 1.15 * SALARY
WHEN CATEGORY = 'D' THEN 1.12 * SALARY
END
WHERE CATEGORY in ('A','B','C','D')
0

Commented:
@jagssidurala: your solution is the same as mine :-)
0

Sr. Software EnginnerCommented:
UPDATE                              EMPLOYEE
SET                                     SALARY = CASE
WHEN CATEGORY = 'A' AND CATEGORY = 'B' THEN 1.2 * SALARY
WHEN CATEGORY = 'C' THEN 1.15 * SALARY
WHEN CATEGORY = 'D' THEN 1.12 * SALARY
END
WHERE CATEGORY in ('A','B','C','D')
0

Commented:
@rbride: In your query salary multiplication has placed after the case statement. as per my knowledge it do not works.

@h4hardy: you can not put 'AND' for 'A', 'B' as we are updating entire table, No record is having 'A' and 'B'.

So 'OR' is correct
0

Director of Software EngineeringCommented:
jagssidurala, rbride statement will work. We can also write as given below. Am I right?

UPDATE Employee
SET salary =salary+ (salary *
CASE
WHEN CATEGORY in ('A', 'B') THEN  cast(20 as float)/cast(100 as float)
WHEN CATEGORY='C' THEN  cast(15 as float)/cast(100 as float)
WHEN CATEGORY='D'  THEN  cast(12 as float)/cast(100 as float)
END)
WHERE CATEGORY in ('A','B','C','D')
0

Commented:
@shasunder: you can write it like that but because you already know the answer to cast(20 as float)/cast(100 as float) it is more efficient for the server (and simpler to read) to just write 0.2.

0

Commented:
you can write a simple query containing multiple quires. like that

UPDATE Employee SET salary=1.2*salary WHERE Category="A" OR CATEGORY='B';
UPDATE Employee SET salary=1.15*salary WHERE Category="C";
UPDATE Employee SET salary=1.12*salary WHERE Category="D";

Execute the query..

It so simple not complex
0

Author Commented:
Thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.