How to calulate a number in a table from a different field in the same table?

Question

Say I have a basic table, products, with 3 fields, productname, cost, and costnotax.

Is it possible to automatically fill in the costnotax field by subtracting a percentage from the cost field?

Answer 1

Yes.

Here's two examples, using where the tax is 25% on the original price. This is for the whole table:

UPDATE table SET costnotax = cost / 1.25;

And this is for specific rows:

UPDATE table SET costnotax = cost / 1.25 WHERE productname = 'Piglet with cream';

Answer 2

Yes - simply something like this to calculate what the original cost was if you've got, say, 6% tax

UPDATE mytable SET costnotax=cost/1.06

However, there's little point in storing it when you can select it just as easily

SELECT cost, cost/1.06 AS costnotax FROM mytable;

Answer 3

If you need to do it for each new or updated record , then it should be BEFORE INSERT/UPDATE trigger. If you need to it once for all/specific records in the table, use UPDATE .... If all you need is to show costnotax, then it doesn't have to be a column at all, you can just query it like SELECT cost/1.25 as costnotax FROM ...