Solved

Update query

Posted on 2011-09-14
9
216 Views
Last Modified: 2012-05-12
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
Comment
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
  • Learn & ask questions
9 Comments
 
LVL 4

Accepted Solution

by:
rbride earned 500 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

by:jagssidurala
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

by:rbride
ID: 36536113
@jagssidurala: your solution is the same as mine :-)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:h4hardy
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

by:jagssidurala
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

by:Shanmuga Sundaram
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

by:rbride
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

by:smartcheater
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";

Open in new window


Execute the query..

It so simple not complex
0
 

Author Closing Comment

by:rajeeva_nagaraj
ID: 36896803
Thanks a lot
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question