Oracle 10g Add primary key as sequence for existing table

Question

I have three tables- Member,Payment,Award with over 3000 of records on them. At present Member table has memberId as a primary key and memberid is foreign key to Payment and Award tables. Note memberid is not auto generated field.This tables were created in Access. My job is to migrate into Oracle and add Primary key let's say mem_id as sequence number to the Member table and add mem_id as foreign key to Payment and Award tables.

• I have migrated the tables in Oracle.

• Added column name mem_id to member table as

  alter table member add mem_id number Unique;

• created a sequence

        create Sequence Mem_seq
        START WITH 1
        MAXVALUE 999999999
        MINVALUE 1
        NOCYCLE
        NOCACHE
        NOORDER;
  

• since mem_id is null for all records, I update the column

     Update member 
     Set mem_id =  mem_seq.nextval
     Where mem_id IS NULL;
  

So far it works good, Now my challenge to add Foreign key to Payment table as well as Award table - created the mem_id column in Payment table

       alter table PAYMENT
        add mem_id number Unique;

• My thought is to update the mem_id in Payment table

• Update statement is

     update PAYMENT
     set PAYMENT.mem_id = MEMBER.mem_id
     where PAYMENT.memberid = MEMBER.memberid; 
  

I get error ORA-00904 member.memberid: invalid identifier Need help to solve this problem.

Answer 1

Try updating PAYMENT table with the following

update payment p set p.mem_id  = (select mem_id from member where 
memberid = p.memberid);