Oracle – Populate Column with Sequence Value Generated for Identity Column

jdbcoraclesequence

I have a table that has a column that is determined by a sequence using the identity column thing, so after a record is inserted (or during, I'm not sure how it works behind the scenes, only that it does) it gets a number one higher than the previous record.

I'm writing a program where there is a condition that leads to a record having that number also be in another column. Is there a way to do this without just making the record and then modifying it immediately afterwards?

Thanks!

Edit: My code:

String insertion = "INSERT INTO DANIEL.UNIQUE_PHYSICIAN "
                    + "(SOURCE_ID, ROOT_ID, SOURCE_TABLE, CONFIDENCE_IS_SAME,"
                    + " IS_ROOT_PHYS, FIRST_NAME, LAST_NAME, POSTAL_CODE) VALUES "
                    + "(" + p.getPhysicianID() + ", " + Integer.toString(root_id) + ", STI.PHYSICIAN, 1, 0, " + p.getFirstName()
                    + ", " + p.getLastName() + ", " + p.getPostalCode() + ");";

and the table:

CREATE TABLE UNIQUE_PHYSICIAN 
(
  SOURCE_ID NUMBER 
, ROOT_ID NUMBER 
, SOURCE_TABLE VARCHAR2(20 BYTE) 
, CONFIDENCE_IS_SAME FLOAT(126) 
, IS_ROOT_PHYS CHAR(1 BYTE) NOT NULL 
, UNIQUE_ID NUMBER NOT NULL 
, FIRST_NAME VARCHAR2(20 BYTE) 
, LAST_NAME VARCHAR2(20 BYTE) 
, POSTAL_CODE VARCHAR2(20 BYTE) 
, CONSTRAINT UNIQUE_PHYSICIAN_PK PRIMARY KEY 
  (
    UNIQUE_ID 
  )
  USING INDEX 
  (
      CREATE UNIQUE INDEX UNIQUE_PHYSICIAN_PK ON UNIQUE_PHYSICIAN (UNIQUE_ID ASC) 
      LOGGING 
      TABLESPACE DATAL03 
      PCTFREE 10 
      INITRANS 2 
      STORAGE 
      ( 
        INITIAL 65536 
        MINEXTENTS 1 
        MAXEXTENTS UNLIMITED 
        BUFFER_POOL DEFAULT 
      ) 
      NOPARALLEL 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE DATAL03 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS UNLIMITED 
  BUFFER_POOL DEFAULT 
) 
NOCOMPRESS 
NOPARALLEL;

In this instance, I want to set ROOT_ID to be equal to the UNIQUE_ID assigned to that record.

Best Answer

If you have a condition that specifies 1005 whether root_id has to get the same value as unique_id, you could use a TRIGGER. Something like this:

CREATE OR REPLACE TRIGGER root_unique_trg
    BEFORE INSERT
    ON UNIQUE_PHYSICIAN
    FOR EACH ROW
    WHEN (NEW.is_root_phys = 1)
BEGIN
    :NEW.root_id := :NEW.unique_id ;
END ;

Tested in SQLfiddle