Solved

Update query

Posted on 2011-09-14
9
225 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

696 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