Solved

Update query

Posted on 2011-09-14
9
205 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

815 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now