insertion problems
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: insertion problems

  1. #1
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74

    insertion problems

    Table_1:

    Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Col 8 Col 9
    (PK1) (PK2) (PK3) (PK4) (PK5) (PK6) blah blah blah
    1 A raj 123 Abc 321 ….. …… ……
    1 A raj 123 xyz 321 ….. …… ……


    I want to select data from the above table and insert into the same table with a change in PK5 and PK6. the code that I’m using is something like this:
    INSERT INTO table_1 t1
    SELECT t2.pk1, t2.pk2, t2.pk3, t2.pk4, ‘mno’, 999, t2.blah, t2.blah,…………
    from table_2 t2 where t2.pk4 in (123, 234)
    and t2.pk6 = 321;

    but I get error because the primary key is being duplicated, which is fair. This is what it is trying to do.

    Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Col 8 Col 9
    (PK1) (PK2) (PK3) (PK4) (PK5) (PK6) blah blah blah
    1 A raj 123 mno 999 ….. …… ……
    1 A raj 123 mno 999 ….. …… ……

    My question is, is there anyway to avoid these duplicate rows?????????
    rajorcl

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    use not exists.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you can disable the PK and insert, when enable your PK use an exception table

    ALTER TABLE your_table
    ENABLE VALIDATE CONSTRAINT your_pk
    EXCEPTIONS INTO except_table;

    then just use that exception table to delete the duplicate data

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Why would the excersice of
    1) Disable PK
    2) Insert Recs
    3) Enable with execptions in ex table.

    be done when it can be done in query while insert recs.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I am not so sure NOT EXISTS would work in his case

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Code:
    INSERT INTO 
      table t1
    SELECT 
      t2.pk1  , 
      t2.pk2  , 
      t2.pk3  , 
      t2.pk4  , 
      ‘mno’   , 
      999     , 
      t2.blah , 
      t2.blah
    from 
      table t2 
    where 
      t2.pk4 in (123, 234)  and
      t2.pk6 = 321          and 
      NOT EXISTS 
            ( 
             Select 
               NULL 
             from 
               table t1 
             where 
               t1.pk1  = t2.pk1    and
               t1.pk2  = t2.pk2    and
               t1.pk3  = t2.pk3    and
               t1.pk4  = t2.pk4    and 
               t1.pk5  =  'mno'    and 
               t1.pk6  =  999 
            )
    ;

    Abhay.
    Last edited by abhaysk; 09-11-2003 at 08:34 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74

    still doesn't work

    Thanks to all of you, but nothing seems to work so far. Because I cant use the except table on production as per the company rules. And Abhay i tried not exists before I posted it here, so which is obvious that it wouldn't work. And the union works only when i select the contents of the primary key. So is there anyway that i could make the union work only on pk columns but select the all the columns from the table ???????

    thanks in advance
    rajorcl

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: still doesn't work

    Originally posted by kanthbethi
    And Abhay i tried not exists before I posted it here, so which is obvious that it wouldn't work.
    I wonder how did it work for me...and still it do, it only depends on how you may/will structure your query.

    PS

    Code:
    TEST:ABHAY> Create Table Test_PK(ID1 number, ID2 number, ID3 Number, ID4 Number, ID5 NUmber, ID6 Number,
      2  ID7 Number, ID8 Number, constraint pk_test_pk primary key (ID1,ID2,ID3,ID4,ID5,ID6));
    
    Table created.
    
    TEST:ABHAY> Begin for i in 1..100 loop insert into Test_PK values(i,i+1,i+2,i+3,i+4,i+5,i+6,i+7); end loop;end;
      2  /
    
    PL/SQL procedure successfully completed.
    
    TEST:ABHAY> commit;
    
    Commit complete.
    
    TEST:ABHAY> select count(*) from test_pk;
    
                COUNT(*)
    --------------------
                     100
    
    TEST:ABHAY> insert into test_pk select ID1,ID2,ID3,ID4,50,51,id7,id8 from
      2  test_pk t1 where ID4 Between 33 and 66 and ID6 between 34 and 67;
    insert into test_pk select ID1,ID2,ID3,ID4,50,51,id7,id8 from
    *
    ERROR at line 1:
    ORA-00001: unique constraint (ABHAY.PK_TEST_PK) violated
    
    
    TEST:ABHAY> insert into test_pk
      2  select ID1,ID2,ID3,ID4,50,51,id7,id8
      3  from
      4  test_pk t1
      5  where (ID4 Between 33 and 66) and (ID6 between 34 and 67) and
      6  not exists ( select null from test_pk t2 where t1.id1=t2.id1 and t1.id2=t2.id2 and t1.id3=t2.id3 and
      7  t1.id4=t2.id4 and t1.id5=50 and t1.id6=51 );
    
    32 rows created.
    
    TEST:ABHAY> commit;
    
    Commit complete.
    You see below the way query's used which will exclude the row that will error while inserting.

    Code:
    TEST:ABHAY> select count(*) from ( select ID1,ID2,ID3,ID4,50,51,id7,id8
      2  from
      3  test_pk t1
      4  where (ID4 Between 33 and 66) and (ID6 between 34 and 67) and
      5  not exists ( select null from test_pk t2 where t1.id1=t2.id1 and t1.id2=t2.id2 and t1.id3=t2.id3 and
      6  t1.id4=t2.id4 and t1.id5=50 and t1.id6=51 ) );
    
                COUNT(*)
    --------------------
                      32
    
    TEST:ABHAY> select count(*) from ( select ID1,ID2,ID3,ID4,50,51,id7,id8
      2  from
      3  test_pk t1
      4  where (ID4 Between 33 and 66) and (ID6 between 34 and 67)
      5  );
    
                COUNT(*)
    --------------------
                      33
    
    TEST:ABHAY> select ID1,ID2,ID3,ID4,50,51,id7,id8
      2  from
      3  test_pk t1
      4  where (ID4 Between 33 and 66) and (ID6 between 34 and 67)
      5  minus
      6  select ID1,ID2,ID3,ID4,50,51,id7,id8
      7  from
      8  test_pk t1
      9  where (ID4 Between 33 and 66) and (ID6 between 34 and 67) and
     10  not exists ( select null from test_pk t2 where t1.id1=t2.id1 and t1.id2=t2.id2 and t1.id3=t2.id3 and
     11  t1.id4=t2.id4 and t1.id5=50 and t1.id6=51 ) ;
    
                     ID1                  ID2                  ID3                  ID4                   50                   51                  ID7                  ID8
    -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
                      46                   47                   48                   49                   50                   51                   52                   53
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

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


Click Here to Expand Forum to Full Width