SQL: Insert data as a function of other locations in a table

Question

In programs like Excel I can have a cell's value equal to some function of other cells in the table. For example typing "=C1*D1" into the value of cell E1 will cause E1 to equal the product of the other two cells. This product is automatically updated if the values of C1 or D1 are changed.

Is there any way to create similar functionality in SQL? I know I could handle this in the server's python code side of things by changing all database assignments I can find of these values to also change the relevant data that depends on them.

I'm stuck modifying an existing several thousand line server program and I'd prefer if there was a way to do this that doesn't rely on me not missing some database reference somewhere in the code.

When inserting: http://dev.mysql.com/doc/refman/5.1/en/insert-select.html — 000, Jun 25 '13 at 15:34
Yes, it's known as a computed column but I'm not sure that MySQL supports it, I'll have a look at the documentation. — twoleggedhorse, Jun 25 '13 at 15:37
Looks like triggers are the only way: http://stackoverflow.com/questions/5222044/column-calculated-from-another-column — twoleggedhorse, Jun 25 '13 at 15:50

score 1 · Accepted Answer · answered Jun 25 '13 at 15:39

1

You could look at using a trigger on insert/update. In a sample table with three columns (HoursWorked, HourlyRate, TotalPay) where the TotalPay = HoursWorked * HourlyRate, you could add a trigger to the table to calculate TotalPay when the row is inserted or updated.

answered Jun 25 '13 at 15:39

Chad Cook

619
5
15

Thanks, I'll look up triggers and make sure they do what I'd need from them! – Hawkwing Jun 25 '13 at 16:27
Hmm.... they'll be a little tricky to use, but should work fine after some trial and error. Thanks again for your help. This link: http://www.databasedesign-resource.com/mysql-triggers.html may help anyone else trying to follow in my footsteps – Hawkwing Jun 25 '13 at 19:31

SQL: Insert data as a function of other locations in a table

1 Answers1