CODEWITHSUNDEEP
mysql
zouhair zouita
zouhair zouita

Reputation: 155

update a VIRTUAL GENERATED column

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.

Upvotes: 4

Views: 20445

Answers (2)

ranit
ranit

Reputation: 427

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

Upvotes: 1

Shadow
Shadow

Reputation: 34255

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.

Upvotes: 10

Related Questions