insert based on condition
- From: Mohammed Mehraj Hussain <mhdmehraj@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 18 Aug 2009 18:11:11 +0530
hi ALL,
i have two tables , i need to insert the data from pb_missing table to
pb_trnsfr table.
insert into pb_trnsfr select * from pb_missing_move; (but i need to add some
conditions)
for example:
SQL> desc pb_trnsfr
Name Null? Type
----------------------------------------- -------- ----------------
CASE_ID NOT NULL NUMBER(9)
TRN_ID NOT NULL VARCHAR2(50)
SRC_EQ_NAME NOT NULL VARCHAR2(40)
DEST_EQ_NAME NOT NULL VARCHAR2(40)
STREAM_NAME VARCHAR2(32)
TRN_TYPE VARCHAR2(32)
START_DT_TM DATE
END_DT_TM DATE
IN_USE NOT NULL NUMBER(1)
SQL> desc pb_missing_move
Name Null? Type
----------------------------------------- -------- ----------------------------
CASE_ID NOT NULL NUMBER(9)
PHYS_ID VARCHAR2(20)
SRC_EQ_NAME NOT NULL VARCHAR2(40)
DEST_EQ_NAME NOT NULL VARCHAR2(40)
STREAM_NAME VARCHAR2(32)
OBJECTIVE_FUNCTION NUMBER
START_DT_TM DATE
END_DT_TM DATE
IN_USE NOT NULL NUMBER(1)
Before Inserting, i need to compare the data of two tables based on
case_id,src_eq_name,dest_eq_name,stream_name.
if the data which is going to be inserted is already there then
we need to insert it by changing the trn_id for the selected data. In which
the new trn_id is retrived from a sequence (pb_seq).
if the data is not there in the pb_trnsfr, then i need to insert the row by
changing the tran_id ,
here the trn_id is calculated by
*for* each case_id , trn_id = max(trn_id)+1.
the data distributed should be like this ,for example
case_id trn_id
10 1
10 2
10 3
10 4
11 1
11 2
11 3
11 4
responses are highly appreciated ....
Other related posts:
