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

use not exists.

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

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.

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

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.

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

Quote:

---

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.