Re: SQL Insert Statements syntax question

> I have two tables
>
> table1
>
> SPECIAL_HANDLE_ID NOT NULL NUMBER(15)
> TRANSACTION_ID NOT NULL NUMBER(15)
> SPECIAL_CODE_ID NOT NULL NUMBER(15)
> NEED_VERIFY_FLAG NOT NULL VARCHAR2(3)
> ENABLED_FLAG NOT NULL VARCHAR2(1)
> ENABLED_FLAG_DATE DATE
> CREATED_BY NOT NULL VARCHAR2(25)
> CREATED_DATE NOT NULL DATE
> LAST_UPDATE_BY VARCHAR2(25)
> LAST_UPDATE_DATE DATE
>
>
> table2
>
> TRANSACTION_ID NOT NULL NUMBER(15)
> COMP_ADDRESS_ID NUMBER(15)
> ENABLED_FLAG VARCHAR2(1)
> ENABLED_FLAG_DATE DATE
> CREATED_BY NOT NULL VARCHAR2(25)
> CREATED_DATE NOT NULL DATE
> LAST_UPDATE_BY VARCHAR2(25)
> LAST_UPDATE_DATE DATE
>
> I need to insert into table 1 values that meet a certain condition in
> table2
>
> in table1
>
> the first column is a sequence generated number as defined in a
> sequence
> second column is the transaction_id from table 2 that meets specific
> requirements defined by a subquery
>
> select transaction_id from table 2 where transaction_id not in
> (select a.transaction_id from table1 a, table2 b
> where b.comp_address_id = 139 and a.transaction_id =
> b.transaction_id))

What's wrong with

insert into table1
select
  seq.nextval,
  transaction_id,
  '32',
  ENABLED_FLAG,
  ENABLED_FLAG_DATE,CREATED_BY,CREATED_DATE,LAST_UPDATE_BY,LAST_UPDATE_BY from
  table 2 where transaction_id not in

          (select a.transaction_id from table1 a, table2 b
           where b.comp_address_id = 139 and  
           a.transaction_id = b.transaction_id))
  

 

> third column will be a certain value number ('32')
> and the last 7 columns will be coming from table 2 directly from the
> second columns subquery returned rows
>
> How do I create the insert statement in sql?
>
> My problem is that Im not sure how the syntax will go when I have
> specific conditions for specific columns in the table I am inserting
> into- some columns are specific values and some are dependent on the
> subquery.
>
> Any Help would be appreciated
>
> Scott

In the future, please post table schemas as CREATE TABLEs and sample data as INSERTs, and specify what it is you want returned using the sample data. Narratives tend to be ambiguous.

Rene Nyffenegger

-- 
  no sig today