I have the following table called table_1

I create a new column_3

then I create a sequence

CREATE SEQUENCE "sequence_1" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOPARTITION

and I alter the table_1 so the default for new values on column_3 are the the values of the sequence.

ALTER TABLE table_1 MODIFY column_3 DEFAULT sequence_1.NEXTVAL

How can I replace the existing null values on column_3 with the values from this sequence? The final result should be:

CodePudding user response：

Solution I came up with is the following - happy to accept better answers:

ALTER TABLE table_1 ADD column_3 NUMBER(38,0) DEFAULT sequence_1.NEXTVAL NOT NULL

CodePudding user response：

If your DB version is 12c , then adding an identity column will handle what's needed such as

ALTER TABLE table_1 ADD new_id INT GENERATED ALWAYS AS IDENTITY

where

• using GENERATED and AS IDENTITY are mandatory
• There are three following options [ ALWAYS | BY DEFAULT [ ON NULL ] ]. A value for the identity column is always generated if the ALWAYS option is used as in this case. Attempt to insert a value into the identity column will cause an error.
• No need to use START WITH and INCREMENT BY options for the current case. Since those are already 1 as default

Demo