'SQL query - How to increase the salary of employees, without exceeding a certain salary limit

I am trying to get a query that will allow me to increase the salary of people who earn less than 2000, but I don't want the salary increase for these people to be higher than 2000.

The table that I am using is set-up like this:

DECLARE @Employee TABLE
(
    Id INT IDENTITY(1,1) PRIMARY KEY
   ,FirstName NVARCHAR(100)
   ,Surname NVARCHAR(100)
   ,Salary MONEY
) 
INSERT @Employee (FirstName, Surname, Salary) VALUES 
('Michael', 'Barker', 2750), ('Robert', 'Morton', 1550),
('John', 'Mitchell', 1890), ('William', 'Davison', 1840),
('James', 'Houston', 1800), ('Mark', 'Parsons', 2060),
('David', 'Higgins', 1950), ('Richard', 'Frost', 1470),
('Frank', 'Herbert', 2100), ('Brian', 'Matthews', 1930)

I am also using a variable for the salary increase, that looks like this:

DECLARE @SalaryIncreaseInPercentage DECIMAL(16, 2) = 10

The best idea that I could come up with is to use a CASE statement. How do i improve the code so the newly increased salary stops at 2000?

The code I wrote to far looks like this: 

Update @Employee
SET Salary =  CASE
WHEN Salary<2000 THEN((@SalaryIncreaseInPercentage/ 100) * Salary) + Salary
ELSE Salary
END
sqlsql-serversql-updatecase


Solution 1:[1]

You can utilise MIN and VALUES functions

DECLARE @IncreaseFactor DECIMAL(16, 2) = 10

UPDATE e SET e.Salary = (SELECT MIN(NewSalary) 
    FROM (VALUES (e.Salary * (1 + @IncreaseFactor/100)), (2000)) AS salaries(NewSalary))
FROM @Employee e
WHERE e.Salary < 2000

Solution 2:[2]

I would only modify a little on your original query: you do not want to check if the original salary is under 2000 but if the new salary is under 2000:

UPDATE @Employee
SET Salary =  CASE
    WHEN (((@SalaryIncreaseInPercentage / 100) * Salary) + Salary) < 2000 
        THEN ((@SalaryIncreaseInPercentage / 100) * Salary) + Salary
        ELSE WHEN Salary < 2000
            THEN 2000
            ELSE Salary
END

Solution 3:[3]

UPDATE    Employee
SET       Salary =  CASE
WHEN      Salary<2000 THEN((@SalaryIncreaseInPercentage/ 100) * Salary) + Salary
ELSE      Salary
END

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Jeremy Caney

Related Questions