Example: Updating IDENTITY defined as GENERATED BY DEFAULT

Create a table with primary key as an IDENTITY column defined as GENERATED BY DEFAULT and insert data to the table.

CREATE TABLE Test_sqlUpdateByDefault (
    idValue INTEGER GENERATED BY DEFAULT AS IDENTITY,
    acctNum LONG,
    name STRING,
primary key(acctNum))

INSERT INTO Test_sqlUpdateByDefault VALUES (DEFAULT, 123456, 'joe')

INSERT INTO Test_sqlUpdateByDefault VALUES (400, 23456,'sam')

INSERT INTO Test_sqlUpdateByDefault VALUES (500, 34567,'carl')

Table Test-sqlUpdateByDefault will have the following rows:

1, 123456, 'joe'
400, 23456, 'jasmine'
500, 34567, 'carl'

Example 7-37 Update IDENTITY column defined as GENERATED BY DEFAULT

UPDATE Test_sqlUpdateByDefault 
SET idValue = 100 
WHERE acctNum = 123456

The UPDATE statement above replaces the row (1, 123456, 'joe') with (100, 123456, 'joe') in the database.