Re: insert based on condition

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 18 Aug 2009 13:51:43 +0100
Message-ID: <7765c8970908180551k6cb9d9c3jec527daed6414d74_at_mail.gmail.com>

lookup the syntax and usage of the merge command. (or if that's not in your version write two statements, an insert and an update). Actually now I've looked at your case more closely and made some guesses based on the column names, I'd suggest that you write the insert for missing data and consider the update case very carefully to make sure it achieves what the business/data model expects - I'd usually experiment with a single known row to start with.

cheers

Niall

On Tue, Aug 18, 2009 at 1:41 PM, Mohammed Mehraj Hussain < mhdmehraj_at_gmail.com> wrote:

> 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 ....

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 18 2009 - 07:51:43 CDT