Solved

# Update query

Posted on 2011-09-14
Medium Priority
226 Views
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
0
Question by:rajeeva_nagaraj
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 4

Accepted Solution

rbride earned 2000 total points
ID: 36534385
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

LVL 12

Expert Comment

ID: 36534928
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

LVL 4

Expert Comment

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

LVL 4

Expert Comment

ID: 36536133
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

LVL 12

Expert Comment

ID: 36537635
@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

LVL 17

Expert Comment

ID: 36538485
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

LVL 4

Expert Comment

ID: 36541607
@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

LVL 1

Expert Comment

ID: 36543355
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 Closing Comment

ID: 36896803
Thanks a lot
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
###### Suggested Courses
Course of the Month12 days, 23 hours left to enroll