CODEWITHSUNDEEP
sqloracle-databasesql-updateoracle12c
Vinoth Karthick
Vinoth Karthick

Reputation: 1087

Update sequence Number in a new Column in Table

Input

create table myprojects ( startdate date, enddate date, profit  number);        
insert into myprojects (startdate, enddate,profit ) values(to_date('1/5/2019','mm/dd/yyyy'),to_date('5/1/2019','mm/dd/yyyy'),100);        
insert into myprojects (startdate, enddate,profit ) values(to_date('1/6/2019','mm/dd/yyyy'),to_date('2/10/2019','mm/dd/yyyy'),50);   
alter table myprojects add ( projectid number );

Now, I have added the new column in this table. How to Populate the values for this column like 1,2 for each row?

Using the below statement, it works fine.

CREATE SEQUENCE myseq  START WITH 1   INCREMENT BY 1   CACHE 20 
/
update myprojects set projectid=myseq.nextval

Question

  1. However,using the below statement it just updates 1 to all records. Why?

    update myprojects set projectid=(select nvl(max(projectid),0) +1  from myprojects)

  2. Is there any way to achieve the same without creating or using sequence?

Upvotes: 0

Views: 1623

Answers (3)

Popeye
Popeye

Reputation: 35930

If you are using Oracle 12c and above and if you are happy with identity column then you can use the following alter table command and oracle will generate numbers for you.

alter table myprojects add ( projectid number generated always as identity);

db<>fiddle demo

Please read about Identity column from Oracle docs before using it in your actual project.

Cheers!!

Upvotes: 2

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65433

Because all values for projectid column are null at the beginning and you're trying to update all values at the same time without any filtering condition . Consider using row_number() analytic function to produce consecutive unique integers :

update myprojects p
   set projectid = ( with t as ( 
                         select m.*,
                                row_number() 
                                     over (order by startdate, enddate, profit) as rn
                           from myprojects m
                    )  
                    select rn
                      from t
                     where t.startdate = p.startdate
                       and t.enddate = p.enddate
                       and t.profit = p.profit
                     );

as an alterative way. All the columns of the table(startdate, enddate, profit) used within the row_number() function's order by clause and within the matching conditions (t.startdate = p.startdate and t.enddate = p.enddate and t.profit = p.profit) of where condition to get the uniqueness for the updating operation.

Demo

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271171

Here is one method using merge:

merge into myprojects p
      using (select p.*, row_number() over (order by startdate) as x from myprojects p) pp
      on (p.rowid = pp.rowid)
when matched then update set projectid = x;

Upvotes: 1

Related Questions