asked on

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

ASKER CERTIFIED SOLUTION

rbride

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jagssidurala

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')

rbride

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

h4hardy

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')

jagssidurala

@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

Shanmuga Sundaram D

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')

rbride

@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.

smartcheater

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";

Open in new window

Execute the query..

It so simple not complex

rajeeva_nagaraj

ASKER

Thanks a lot