CODEWITHSUNDEEP
sqloracle-database
Bennyz
Bennyz

Reputation: 623

Oracle - updating a sorted table

I found an old table without a primary key, and in order to add one, I have to add a new column and fill it with sequence values. I have another column which contains the time of when the record was created, so I want to insert the sequence values to the table sorted by the column with the time.

I'm not sure how to do it. I tried using PL\SQL - I created a cursor for a query that returns the table with an ORDER BY, and then update for each record the cursor returns but it didn't work.

Is there a smart working way to do this?

Thanks in advance.

Upvotes: 0

Views: 1445

Answers (3)

Alex Poole
Alex Poole

Reputation: 191265

Another option is just to use a correlated subquery, with the wrinkle of a nested subquery to generate the row number. Setting up some sample data:

create table t42 (datefield date);
insert into t42 (datefield) values (sysdate - 7);
insert into t42 (datefield) values (sysdate + 6);
insert into t42 (datefield) values (sysdate - 5);
insert into t42 (datefield) values (sysdate + 4);
insert into t42 (datefield) values (sysdate - 3);
insert into t42 (datefield) values (sysdate + 2);

select * from t42;

DATEFIELD
---------
12-JUL-12
25-JUL-12
14-JUL-12
23-JUL-12
16-JUL-12
21-JUL-12

Then adding and populating the new column:

alter table t42 add (id number);

update t42 t1 set t1.id = (
    select rn from (
        select rowid, row_number() over (order by datefield) as rn
        from t42
    ) t2
    where t2.rowid = t1.rowid
);

select * from t42 order by id;

DATEFIELD         ID
--------- ----------
12-JUL-12          1
14-JUL-12          2
16-JUL-12          3
21-JUL-12          4
23-JUL-12          5
25-JUL-12          6

Since this is a synthetic key, making it match the order of another column seems a bit pointless, but I guess doesn't do any harm.

To complete the task:

alter table t42 modify id not null;

alter table t42 add constraint t42_pk primary key (id);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One simple way is to create a new table, with new column an all other columns:

create table newt (
    newtID int primary key not null,
    . . .
)

Then insert all the old data into it:

insert into newt
    select row_number() over (order by <CreatedAt>), t.*
    from t

(You can substitute all the columns in, instead of using "*". Having the columns by name is the better practice. This is shorter, plus, I don't know the column names.)

If you alter the table to add the column, then the column will appear at the end. I find that quite awkward for the primary key. If you do that, though, you can update it as:

with t as (select row_number() over (order by <CreatedAt>) as seqnum, t.*
           from t
          )
update t
    set newtID = seqnum

Upvotes: 0

dani herrera
dani herrera

Reputation: 51655

  • First of all, create new field and allow null values.

  • Then, update field from other table or query. Best approach is to use merge statement.

Here a sample from documentation:

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*.01)
     WHERE (S.salary <= 8000);

Here sample sentences:

ALTER TABLE 
   customer 
MODIFY 
   ( 
   your_new_field varchar2(100) not null
   )
;
ALTER TABLE 
   customer 
ADD CONSTRAINT customer_pk PRIMARY KEY (your_new_field)
;

Upvotes: 0

Related Questions