Updating Data (practice)-CodePudding

So I'm given a practice query to increase or UPDATE employees salaries by 20% if their rating is above 5.

UPDATE company
SET salary = (salary salary * 20/100)
WHERE rating > 5;
SELECT * FROM company;

My question is,why is it correct to write this part of the code (salary salary) and simply not (salary * 20/100). Thank you.

CodePudding user response：

To increase the salary by 20%, you have to add 20% of the salary to the old salary. For instance, if the salary is 100, you add 20 to to get the new salary 120.

To calculate 20% of something, you multiply it by 20/100. So salary * 20/100 is 20% of the salary.

Then you add this to the old salary, so the complete formula is salary (salary * 20/100).

If you just multiply the salary by 20/100, the result would be 20, not 120.

CodePudding user response：

This is nothing more than elementary mathematics.

Consider what your statement is currently doing: salary = (salary salary * 20/100)

Fortunately, because of order of precedence, multiplication and division is followed by addition, not vice-versa, so you can also read it as salary = salary (salary * (20/100))

20 divided by 100 is 0.2 (ie 20%) so if you simply had salary = salary * 0.2 you can see you'd have 20% of the original salary - not what you want, so you add this 20% to the existing salary to result in the original salary plus 20%.

The more common approach - I believe - is the equivalent and more obvious salary = salary * 1.2, ie multiply the value by itself plus 0.2 of its value.

It follows that you could reverse this to remove 20% by dividing by 1.2