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 asunique_id
, you could use aTRIGGER
. Something like this:Tested in SQLfiddle