#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Posts
    74
    Rep Power
    7

    Multiple table insert involving sequences


    Hi, I have a temp table and want to insert that data into 2 tables. In both of my tables i have a sequence column but in my second table its a foreign key to the main table

    PHP Code:
    $up_query="INSERT INTO AFFILIATE (FNAME,LNAME,EMAIL,PHONE,ORG,ADDRESS1,ADDRESS2,COUNTY_ID,AFF_ID)
                    Select a.FNAME, a.LNAME,a.EMAIL,a.PHONE, a.ORG, a.ADDRESS1, a.ADDRESS2,C.COUNTY_ID,'AF'||aff_seq.nextval 
                    FROM temp_aff A LEFT OUTER JOIN COUNTY C
                    ON UPPER(A.COUNTY)=C.CNAME"
    ;
    $up_query2=    "INSERT INTO TEST4 (DEPT_ID,CAMP_ID,AFF_ID, PASSWD) 
                    SELECT D.DEPT_ID,C.CAMP_ID,aff_seq.currval,'Zq'||dbms_random.string('A',4)||'$8' from dual,TEMP_AFF A 
                    left outer join DEPT D ON (UPPER(A.DEPT)=UPPER(D.DNAME))
                    left outer join CAMPUS C ON (UPPER(A.CAMPUS)=UPPER(C.CPNAME))"

    However in my test4 table its inserting the aff_seq.currval for all records. Its not incrementing as in the affiliate table.
    I appreciate any suggestions which would help me resolve it
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Iran
    Posts
    149
    Rep Power
    139
    Hello there,


    The CURRVAL pseudocolumn returns the current value of the sequence without incrementing/updating the sequence, and that corresponds to the value returned by the sequence when the NEXTVAL was queried for the last time upon that sequence. As a result if you call 100 times CURRVAL, 100 times you get the same value. Here is an example:

    Code:
    SQL> CREATE SEQUENCE my_test_seq;
    
    Sequence created.
    
    SQL> SET SERVEROUTPUT ON;
    SQL> 
    SQL> BEGIN
      2      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.NEXTVAL);
      3  END;
      4  /
    The next value of the sequence is: 1
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> 
    SQL> BEGIN
      2      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.CURRVAL);
      3      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.CURRVAL);
      4      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.CURRVAL);
      5      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.CURRVAL);
      6      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.CURRVAL);
      7  END;
      8  /
    The next value of the sequence is: 1
    The next value of the sequence is: 1
    The next value of the sequence is: 1
    The next value of the sequence is: 1
    The next value of the sequence is: 1
    
    PL/SQL procedure successfully completed.

    Now what I understood from your problem is that the sequence value in the TEST4 table is in fact a foreign key referencing the aff_id (thus a unique index I believe) column in the table temp_aff. What you can do is insert the values as you do now and then do an update of the sequence values accordingly. Something like this

    Code:
    UPDATE test4 t1
    SET aff_id = (SELECT aff_id
                  FROM temp_aff t2
                  WHERE <...>
                 )
    And in the WHERE clause you need to write a condition that links/maps uniquely each row of the table temp_aff to its corresponding line in the table test4. Not having the DDL of your tables I cannot tell you more about that, but just by looking at the column names, I'm not sure whether currently you can do that. Probably you may need to normalize your tables.


    Regards,
    Dariyoosh

IMN logo majestic logo threadwatch logo seochat tools logo