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
rajeeva_nagarajAsked:
Who is Participating?
 
rbrideConnect With a Mentor 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
 
jagssiduralaCommented:
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
 
rbrideCommented:
@jagssidurala: your solution is the same as mine :-)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
h4hardySr. 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
 
jagssiduralaCommented:
@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
 
Shanmuga SundaramDirector 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
 
rbrideCommented:
@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
 
smartcheaterCommented:
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
0
 
rajeeva_nagarajAuthor 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.

All Courses

From novice to tech pro — start learning today.