CODEWITHSUNDEEP
mysqldatabase
atastrophic
atastrophic

Reputation: 3143

Performing Calculation in Default MySQL Value

Is there a way to performing calculations based on value of some other column within same or different table using the Default Value property (that is, the DEFAULT clause on a column definition) of MySQL ?

We can set static default values to any column but can we perform calculations or query other tables' data ??

EDIT

Let's say a table with column for marks and other with total_marks and third column percentage. How to set default value of percentage to be calculated from the former two columns

Upvotes: 2

Views: 6244

Answers (2)

fran
fran

Reputation: 326

The trigger solution is great, but I came here with a slightly different situation and that solution is not what I was looking for.

I am using the default value for the one and only time I add the new column to the table. In any other situation I will not be using that default value. So I don't want to have a trigger.

I think in this case, the only option is to default the value to 0 (or any particular value). Then update all these freshly inserted 0's with the value needed from the calculation.

Finally, update the default value of that new column to whatever is needed from now on.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108420

No. The value for the DEFAULT clause must be a constant. (The one exception to this rule is the use of CURRENT_TIMESTAMP as a default value for a TIMESTAMP column.)

As an alternative, you can use a TRIGGER to set a value for a column when a row is inserted or updated.

For example, within a BEFORE INSERT FOR EACH ROW trigger, you can perform calculations from values supplied for other columns and/or query data from other tables.

EDIT

For the example given in the EDIT of the question, an example starting point for a trigger definition:

CREATE TRIGGER mytable_bi 
BEFORE INSERT ON mytable 
FOR EACH ROW
BEGIN
  SET NEW.percentage = (100.0 * NEW.marks) / NULLIF(NEW.total_marks,0);
END

Upvotes: 9

Related Questions