insertion problems

[kanthbethi]

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

[abhaysk]

use not exists.

[pando]

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

[abhaysk]

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.

[pando]

I am not so sure NOT EXISTS would work in his case

[abhaysk]

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.

[kanthbethi]

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

[abhaysk]

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

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.

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.