Setting commission for employees based on position

I just started to learn some sql and I am using oracle database 11g. The question I have is that I have a staff table where I am trying to update the commission for each staff based on the position of the staff. I wanted to update the commission for regular employees to be 0.04(4%) and 0.05(5%) for managers.

Is there a way to do this all in one statement?

The way I did it was that I just updated all the staff commission by :

UPDATE staff SET commission = 0.04;

then I did another update statement to update just the manger

UPDATE staff SET commission = 0.05 WHERE position = 'Manager';

I was thinking something along the lines of this however this wouldnt work lol.

UPDATE staff SET commission = 0.04 WHERE NOT position = 'Manager' AND SET commission = 0.05
WHERE position = 'Manager';

Thanks for your help! much appreciated!

Answer

You may use a CASE expression here:

UPDATE staff
SET commission = CASE WHEN position = 'Manager'
                      THEN 0.05
                      ELSE 0.04 END;

This assumes that you really want to do a blanket assignment to every non manager with a commission of 0.04.

Advertisement

Answer