Oracle SQL - Create Sequence and add sequence values on new column

Question

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:

Does Col3 have a relationship with Col1? Does it matter that your first row is 1,red,1? Can it be 1,red,982? — Isolated
– Isolated, Commented Sep 9, 2022 at 18:03
This does not answer your question, but if the goal is to get a random number in col3, then one method is something like this. Here I'm just creating a new table, but you get the idea: create table t2 as (select id, color, row_number() over (order by DBMS_RANDOM.RANDOM) as rn from my_table) — Isolated
– Isolated, Commented Sep 9, 2022 at 18:27
@Isolated column_3 has to be filled by values generated by the sequence e.g. using sequence_1.NEXTVAL on each row for column_3. — Stamatis Tiniakos
– Stamatis Tiniakos, Commented Sep 9, 2022 at 18:28

Barbaros Özhan · Accepted Answer · 2022-09-09 20:42:27Z

1

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

answered Sep 9, 2022 at 20:42

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stamatis Tiniakos · Accepted Answer · 2022-09-09 19:33:32Z

0

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

answered Sep 9, 2022 at 19:33

Stamatis Tiniakos

8732 gold badges16 silver badges45 bronze badges

1 Comment

William Robertson Over a year ago

default on null is better in my opinion. Adding an identity column would be even better, but there are a couple of steps to adding an identity column to an existing table.

Collectives™ on Stack Overflow

Oracle SQL - Create Sequence and add sequence values on new column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related