I do like to put in a column the difference of two other, how can I do?

Question

mysql> select * from acoes;

+---------+----------+------------+------------+--------------------+
| idacoes | nomeacao | cotaminima | cotamaxima | volatilidadediaria |
+---------+----------+------------+------------+--------------------+
|       1 | abev4    |      10.20 |      11.80 |               0.00 |
|       2 | petr3    |       8.20 |       9.80 |               0.00 |
|       3 | votr2    |      15.00 |      16.80 |               0.00 |
|       4 | itau3    |      28.20 |      39.80 |               0.00 |
|       5 | mglu5    |      16.20 |      20.80 |               0.00 |
+---------+----------+------------+------------+--------------------+

i wanna put in column 'volatilidadediaria' difference between 'cotamaxima' - 'cotaminima'

+--------------------+
| volatilidadediaria |
+--------------------+
|               1.60 |
|               1.60 |
|               1.80 |
|              11.60 |
|               4.60 |
+--------------------+

Answer 1

have you tried this :

SELECT * ,
        cotamaxima - cotaminima AS volatilidadediaria
FROM acoes

EDIT:

 UPDATE acoes SET volatilidadediaria = cotamaxima - cotaminima

Answer 2

UPDATE acoes
SET volatilidadediaria = cotamaxima - cotaminima

But of course then you have redundancy in your database, which is not very good if these values have to be changed often (or ever).

Answer 3

On an ad-hoc basis you can execute this statement:

UPDATE acoes SET volatilidadediaria = cotamaxima - cotaminima

Unfortunately unlike Sql Server I don't think MySql supports calculated columns, but if you want to keep the column up to date when the source values change, you can use triggers in MySql.

Take a look at this answer.

So you would execute something like this to create your triggers (1 for inserts and 1 for updates):

DELIMITER |

CREATE TRIGGER volatilidadediaria_insert BEFORE INSERT ON acoes
  FOR EACH ROW BEGIN
    SET NEW.volatilidadediaria = NEW.cotamaxima - NEW.cotaminima;
  END;
|

CREATE TRIGGER volatilidadediaria_update BEFORE UPDATE ON acoes
  FOR EACH ROW BEGIN
    SET NEW.volatilidadediaria = NEW.cotamaxima - NEW.cotaminima;
  END;
|

DELIMITER ;