Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: Insert and sub query to insert data

    Hi All,

    Just wondering how to insert data into a table using a sequence no. The table has a Unique key which ofcourse is primary key. It gives me an error message.

    Here is my Query. Any other ways to do.
    ===============================
    INSERT INTO ROUTE_SUFFIX_REF
    (
    route_suffix_ref_id,
    ROUTE_SUFFIX_NAME,
    ROUTE_SUFFIX_DESC,
    LAST_UPDATED_BY,
    LAST_UPDATED_DATE
    )
    select
    ROUTE_SUFFIX_REF_SEQ.NEXTVAl,
    MP_SUFFIX,
    'NAME TO BE EDITED',
    'TEST',
    sysdate from TEMP_ROUTE_DATA where MP_SUFFIX is not null and MP_SUFFIX in (select distinct MP_SUFFIX from temp_route_data);
    ===============================

    It gives me an error message that there is a voilation of Unique key.

    Any help is gretely appreciated.

    Thanks,
    Srinivas'

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question

    I could not reproduce your situation, are you sure you don't have a trigger or something that is causing the error?

    Here is what i have:

    SQL>Create Table Route_Suffix_Ref
    2 (
    3 Route_Suffix_Ref_Id Number(9),
    4 Route_Suffix_Name Varchar2(8),
    5 Route_Suffix_Desc Varchar2(20),
    6 Last_Updated_By Varchar2(8),
    7 Last_Updated_Date Date,
    8 Constraint Xpk_Route_Suffix_Ref
    9 Primary Key (Route_Suffix_Ref_Id)
    10 )
    11 /
    Create Sequence Route_Suffix_Ref_Seq
    /

    Table created.

    SQL> 2
    Sequence created.

    SQL>Insert Into Route_Suffix_Ref
    2 (
    3 Route_Suffix_Ref_Id,
    4 Route_Suffix_Name,
    5 Route_Suffix_Desc,
    6 Last_Updated_By,
    7 Last_Updated_Date
    8 )
    9 Select
    10 Route_Suffix_Ref_Seq.Nextval,
    11 Mp_Suffix,
    12 'Name To Be Edited',
    13 'Test',
    14 Sysdate
    15 From Temp_Route_Data
    16 Where Mp_Suffix Is Not Null
    17 And Mp_Suffix In (
    18 Select Distinct Mp_Suffix From Temp_Route_Data)
    19 /

    362 rows created.

    SQL>select * from route_suffix_ref where rownum <6;

    ROUTE_SUFFIX_REF_ID ROUTE_SU ROUTE_SUFFIX_DESC LAST_UPD LAST_UPDA
    ------------------- -------- -------------------- -------- ---------
    1 AE Name To Be Edited Test 14-AUG-03
    2 MD Name To Be Edited Test 14-AUG-03
    3 SAE Name To Be Edited Test 14-AUG-03
    4 MGR Name To Be Edited Test 14-AUG-03
    5 AE Name To Be Edited Test 14-AUG-03

    5 rows selected.




    Also, this looks redundant:

    where MP_SUFFIX is not null
    and MP_SUFFIX in (
    select distinct MP_SUFFIX from temp_route_data);

    Could be just:

    where MP_SUFFIX is not null

    ;D
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    create a trigger that inserts a sequence every time you insert a row.

    I found it very handy.
    Then you never have to worry about that column at all when inserting.

    Below is an example.

    PHP Code:
    drop sequence zero_seq2;

    create sequence zero_seq2;

    create or replace trigger table_xindex_sequence
        before insert on TABLE_XINDEX
        
    for each row
            
    declare
                
    v_seq number;
            
    begin
                select zero_seq2
    .nextval into v_seq from dual;
            if 
    INSERTING then
                
    :new.column_seq := v_seq;
            
    end if;
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •