CODEWITHSUNDEEP
sqlsql-serverpostgresqlt-sqlsql-server-2008-r2
Sizejaul
Sizejaul

Reputation: 93

Update table column values based on conditional logic

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:

Upvotes: 8

Views: 30594

Answers (4)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

This should work:

UPDATE T
SET T.Salary += CASE
        WHEN T.salary BETWEEN 10000 AND 15000 THEN 5000
        WHEN T.salary BETWEEN 15000 AND 20000 THEN 7000
        WHEN T.salary BETWEEN 20000 AND 30000 THEN 80000
        WHEN T.salary BETWEEN 40000 AND 60000 THEN 10000
        ELSE 0
    END
FROM YourTable AS T

Upvotes: 1

Tanner
Tanner

Reputation: 22743

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

Upvotes: 2

chridam
chridam

Reputation: 103375

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

Upvotes: 8

Saba
Saba

Reputation: 385

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

Upvotes: 2

Related Questions