Updating an identity column

 

You can update the value in an identity column to a specified value or have the system generate a new value.

For example, using the table with columns called ORDERNO (identity column), SHIPPED_TO (varchar(36)), and ORDER_DATE (date), you can update the value in an identity column by issuing the following statement:

UPDATE ORDERS   SET (ORDERNO, ORDER_DATE)=
       (DEFAULT, 2002-02-05)
  WHERE SHIPPED_TO = 'BME TOOL'

A value is generated by the system for the identity column automatically. You can override having the system generate a value by using the OVERRIDING SYSTEM VALUE clause:

UPDATE ORDERS OVERRIDING SYSTEM VALUE
  SET (ORDERNO, ORDER_DATE)=
       (553, '2002-02-05')
  WHERE SHIPPED_TO = 'BME TOOL'

 

Parent topic:

Changing data in a table using the UPDATE statement

 

Related reference

Creating and altering an identity column