-
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
-
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"
-
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.
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"
-
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.
Last edited by abhaysk; 09-11-2003 at 07: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"
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|