Example: Updating IDENTITY defined as GENERATED ALWAYS

CREATE TABLE Test_sqlUpdateAlways (
    idValue INTEGER GENERATED ALWAYS AS IDENTITY,
    name STRING,
PRIMARY KEY(idValue));

INSERT INTO Test_sqlUpdateAlways VALUES (DEFAULT, 'joe');
INSERT INTO Test_sqlUpdateAlways VALUES (DEFAULT, 'jasmine');

The Test-sqlUpdateAlways table will have the following rows:

1, 'joe'
2, 'jasmine'

UPDATE Test_sqlUpdateAlways SET idValue = 10 WHERE name=joe;

The above UPDATE statement will raise an exception saying that a user cannot set a value for an IDENTITY column that is defined as GENERATED ALWAYS. An IDENTITY column that is defined as GENERATED ALWAYS cannot be updated. Only the IDENTITY column that is defined as GENERATED BY DEFAULT can be updated.

To resolve this exception and be able to update the IDENTITY column value, you need to alter the IDENTITY column and change the property of the IDENTITY column to GENERATED BY DEFAULT. But there may be implications to the existing data. For more information on how to alter an IDENTITY column see, Altering an IDENTITY Column.