Update Incentive column based on the current salary

Question

CREATE TABLE EMPSAL
(
  ID INT ,
  NAME VARCHAR(40),
  SALARY FLOAT,
  INCENTIVES FLOAT  
)

INSERT INTO EMPSAL VALUES (1,'ABHI',500,NULL)
INSERT INTO EMPSAL VALUES (2,'BABY',600,NULL)
INSERT INTO EMPSAL VALUES (3,'CHARAN',3000,NULL)
INSERT INTO EMPSAL VALUES (4,'DHANA',4000,NULL)
INSERT INTO EMPSAL VALUES (5,'EMO',5000,NULL)
INSERT INTO EMPSAL VALUES (6,'FARAN',6000,NULL)
INSERT INTO EMPSAL VALUES (7,'GEO',7000,NULL)
INSERT INTO EMPSAL VALUES (8,'JAHANGEER',8000,NULL)

Update Incentive column to (SALARY + 5% OF SALARY) if salary is < 1000.

Update Incentive column to (SALARY + 7% OF SALARY) if salary is < 5000.

Update Incentive column to SALARY if salary is above 6000.

I have no clue how to initiate for this query.

Here is your example https://stackoverflow.com/questions/15766102/i-want-to-use-case-statement-to-update-some-records-in-sql-server-2005?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — Hany Habib, May 23 '18 at 10:12
Don't store monetary values as `FLOAT`! Use `decimal`/`numeric`/`money`. — Gordon Linoff, May 23 '18 at 10:33
@GordonLinoff Please let me know the disadvantage of using FLOAT while creating EMPSAL and what are the advantage of using Decimal/Numeric/Money . — Harish Kumar, May 23 '18 at 10:51

score 1 · Answer 1 · answered May 23 '18 at 10:12

1

Just use Case like below:

Update EMPSAL
Set Incentive =
Case
    WHEN Salary < 1000 then SALARY * 1.05  
    WHEN Salary < 5000 AND salary >= 1000 THEN SALARY *  1.07
    ELSE SALARY
End

answered May 23 '18 at 10:12

Hasan Gholamali

633
4
13

score 0 · Accepted Answer · answered May 23 '18 at 10:10

0

UPDATE EMPSAL
SET INCENTIVES = (CASE WHEN Salary < 1000 then SALARY * 0.05  
                      WHEN Salary < 5000 AND salary >1000 THEN SALARY *  0.07
                      ELSE SALARY 
                      END)

answered May 23 '18 at 10:10

Rajat Jaiswal

645
4
15

Cristiano Morgado · Answer 3 · 2018-05-23T10:45:31.460

0

This should work within the rules you specified (though not you have a gap if the salary is between 5000 and 6000 where it's undefined:

UPDATE EMPSAL
SET INCENTIVES = CASE 
        WHEN SALARY < 1000
            THEN SALARY + (SALARY * 0.05)
        WHEN SALARY BETWEEN 1000 AND 5000
            THEN SALARY + (SALARY * 0.07)
        WHEN SALARY >= 6000
            THEN SALARY
        END

edited May 23 '18 at 10:45

answered May 23 '18 at 10:15

Cristiano Morgado

68
3
9

Update Incentive column based on the current salary

3 Answers3