Update table column values based on conditional logic

Question

I have table like this:

name | salary 
Tom  | 10000
Mary | 20000
Jack | 30000
Lisa | 40000
Jake | 60000

I need an update query to update the salary column depending on the values it contains.

Salaries need to increase by:

• 5000 for values between 10000 to 15000
• 7000 for values between 15000 to 20000
• 8000 for values between 20000 to 30000
• 10000 for values between 40000 to 60000

Solution 1

Try using the CASE statement within the UPDATE command

UPDATE
    [yourtablename]
SET
    salary = 
         CASE 
            WHEN salary BETWEEN 10000 AND 15000 THEN salary + 5000 
            WHEN salary BETWEEN 15000 AND 20000 THEN salary + 7000 
            WHEN salary BETWEEN 20000 AND 30000 THEN salary + 8000 
            WHEN salary BETWEEN 40000 AND 60000 THEN salary + 10000 
            ELSE salary
         END 

Solution 2

Something like this:

UPDATE YourTable
SET salary = CASE 
                    WHEN salary > 10000 AND salary <= 15000 THEN salary + 5000
                    WHEN salary > 15000 AND salary <=20000 THEN salary + 7000
                    .
                    .
                    .
                END

Solution 3

Just use an UPDATE statement with a CASE statement with the required logic in it:

UPDATE SalaryTable
SET Salary = 
    (CASE WHEN Salary BETWEEN 10000 AND 14999 THEN Salary + 5000
          WHEN Salary BETWEEN 15000 AND 19999 THEN Salary + 7000
          WHEN Salary BETWEEN 20000 AND 29999 THEN Salary + 8000
          WHEN Salary BETWEEN 40000 AND 59000 THEN Salary + 10000
          ELSE Salary
     END)

I've used BETWEEN which evaluates greater than or equal to and less than or equal to, hence the values like 14999.

Also, you have a gap between 30000 and 40000, which isn't picked up, but I'm assuming this is down to it being dummy data.

Reference:

SQL BETWEEN

SQL CASE

Comments

[Sean Lange]

You would use a case expression. msdn.microsoft.com/en-us/library/ms181765.aspx

[HABO]

The question states increase by, not replace. Changing the = to += would help, although the default value would have to be 0.

[HABO]

This can be simplified using salary = salary + CASE and not including the addition in each individual WHEN clause. Or use salary += CASE.