update a VIRTUAL GENERATED column

Question

UPDATE reservation SET NB_RESTE=1 WHERE IDRESERVATION='28'

i want to update the NB_RESTE value but it wont work , even when i try to update another column it works properly Knowing that NB_RESTE is VIRTUAL GENERATED

error message :

#3105 - The value specified for generated column 'NB_RESTE' in table 'reservation' is not allowed.

Answer 1

As mysql manual on update statement says:

If a generated column is updated explicitly, the only permitted value is DEFAULT.

Which is completely logical, since the value of the field is generated by an expression. In case of a virtual volumn, there is not even a value stored in the table. If you want to change the value of a generated column, then update the field(s) it is generated from.

Answer 2

Virtual generated columns cannot be altered to stored generated columns, or vice versa. To work around this, drop the column, then add it with the new definition.

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL);
ALTER TABLE t1 DROP COLUMN c2;
ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

Source: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/alter-table-generated-columns.html