PK violated When I insert values into a table
I'm new to SQL Oracle. I'm trying to "merge" two tables.
PAYMENT_COMMON ---> TABLE A
(PCO_NUMBER(6,0) NOT NULL,
ID_BUILD NUMBER(5,0) NOT NULL,
NUM_DEPARMENT NUMBER(5,0) NOT NULL,
PGC_DATE_CANCELATION DATE NOT NULL,
PGC_AMMOUNT_PAY NUMBER(10) NOT NULL,
ID_VIA_PAYMENT NUMBER(2,0) NOT NULL);
and a table called payment_common1 (Same rows and columns as payment_common) which had 111 values that don't are in PAYMENT_COMMON.
So I tried to add those values without duplicate it using the query:
INSERT INTO PAYMENT_COMMON(PCO_NUMBER,
ID_BUILD NUMBER,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT)
SELECT * FROM PAYMENT_COMMON
UNION
SELECT * FROM PAYMENT_COMMON1;
but it returns
ORA-00001: unique constraint (SYSTEM.PK_PAYMENT_COMMON) violated
Hope someone could help me to find out my error.
sql oracle merge insert unique
edited Nov 14 '18 at 2:05
ljeabmreosn
571520
asked Nov 14 '18 at 0:10
Diego SantelicesDiego Santelices
32
add a comment |
I'm new to SQL Oracle. I'm trying to "merge" two tables.
PAYMENT_COMMON ---> TABLE A
(PCO_NUMBER(6,0) NOT NULL,
ID_BUILD NUMBER(5,0) NOT NULL,
NUM_DEPARMENT NUMBER(5,0) NOT NULL,
PGC_DATE_CANCELATION DATE NOT NULL,
PGC_AMMOUNT_PAY NUMBER(10) NOT NULL,
ID_VIA_PAYMENT NUMBER(2,0) NOT NULL);
and a table called payment_common1 (Same rows and columns as payment_common) which had 111 values that don't are in PAYMENT_COMMON.
So I tried to add those values without duplicate it using the query:
INSERT INTO PAYMENT_COMMON(PCO_NUMBER,
ID_BUILD NUMBER,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT)
SELECT * FROM PAYMENT_COMMON
UNION
SELECT * FROM PAYMENT_COMMON1;
but it returns
ORA-00001: unique constraint (SYSTEM.PK_PAYMENT_COMMON) violated
Hope someone could help me to find out my error.
sql oracle merge insert unique
edited Nov 14 '18 at 2:05
ljeabmreosn
571520
asked Nov 14 '18 at 0:10
Diego SantelicesDiego Santelices
32
1
Possible duplicate of Constraint Violation Exception ORA-00001
– ljeabmreosn
Nov 14 '18 at 0:25
add a comment |
I'm new to SQL Oracle. I'm trying to "merge" two tables.
PAYMENT_COMMON ---> TABLE A
(PCO_NUMBER(6,0) NOT NULL,
ID_BUILD NUMBER(5,0) NOT NULL,
NUM_DEPARMENT NUMBER(5,0) NOT NULL,
PGC_DATE_CANCELATION DATE NOT NULL,
PGC_AMMOUNT_PAY NUMBER(10) NOT NULL,
ID_VIA_PAYMENT NUMBER(2,0) NOT NULL);
and a table called payment_common1 (Same rows and columns as payment_common) which had 111 values that don't are in PAYMENT_COMMON.
So I tried to add those values without duplicate it using the query:
INSERT INTO PAYMENT_COMMON(PCO_NUMBER,
ID_BUILD NUMBER,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT)
SELECT * FROM PAYMENT_COMMON
UNION
SELECT * FROM PAYMENT_COMMON1;
but it returns
ORA-00001: unique constraint (SYSTEM.PK_PAYMENT_COMMON) violated
Hope someone could help me to find out my error.
sql oracle merge insert unique
edited Nov 14 '18 at 2:05
ljeabmreosn
571520
asked Nov 14 '18 at 0:10
Diego SantelicesDiego Santelices
32
I'm new to SQL Oracle. I'm trying to "merge" two tables.
PAYMENT_COMMON ---> TABLE A
(PCO_NUMBER(6,0) NOT NULL,
ID_BUILD NUMBER(5,0) NOT NULL,
NUM_DEPARMENT NUMBER(5,0) NOT NULL,
PGC_DATE_CANCELATION DATE NOT NULL,
PGC_AMMOUNT_PAY NUMBER(10) NOT NULL,
ID_VIA_PAYMENT NUMBER(2,0) NOT NULL);
and a table called payment_common1 (Same rows and columns as payment_common) which had 111 values that don't are in PAYMENT_COMMON.
So I tried to add those values without duplicate it using the query:
INSERT INTO PAYMENT_COMMON(PCO_NUMBER,
ID_BUILD NUMBER,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT)
SELECT * FROM PAYMENT_COMMON
UNION
SELECT * FROM PAYMENT_COMMON1;
but it returns
ORA-00001: unique constraint (SYSTEM.PK_PAYMENT_COMMON) violated
Hope someone could help me to find out my error.
sql oracle merge insert unique
sql oracle merge insert unique
edited Nov 14 '18 at 2:05
ljeabmreosn
571520
asked Nov 14 '18 at 0:10
Diego SantelicesDiego Santelices
32
edited Nov 14 '18 at 2:05
ljeabmreosn
571520
asked Nov 14 '18 at 0:10
Diego SantelicesDiego Santelices
32
edited Nov 14 '18 at 2:05
ljeabmreosn
571520
edited Nov 14 '18 at 2:05
ljeabmreosn
571520
edited Nov 14 '18 at 2:05
ljeabmreosn
571520
571520
asked Nov 14 '18 at 0:10
Diego SantelicesDiego Santelices
32
asked Nov 14 '18 at 0:10
Diego SantelicesDiego Santelices
32
asked Nov 14 '18 at 0:10
Diego SantelicesDiego Santelices
32
32
1
Possible duplicate of Constraint Violation Exception ORA-00001
– ljeabmreosn
Nov 14 '18 at 0:25
add a comment |
1
Possible duplicate of Constraint Violation Exception ORA-00001
– ljeabmreosn
Nov 14 '18 at 0:25
1
1
Possible duplicate of Constraint Violation Exception ORA-00001
– ljeabmreosn
Nov 14 '18 at 0:25
Possible duplicate of Constraint Violation Exception ORA-00001
– ljeabmreosn
Nov 14 '18 at 0:25
add a comment |
1 Answer
1
active
oldest
votes
You should check which column(s) make up the primary key index (PK_PAYMENT_COMMON). Do this by the following statement:
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'PAYMENT_COMMON'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
But the main problem with your INSERT statement is that rows already exist in the PAYMENT_COMMON table, so don't need to insert them again.
Also, it is better to be explicit than implicit when performing DML statements (INSERT, UPDATE or DELETE). Therefore, avoid SELECT * and specify all of the columns. This helps to avoid unexpected errors if the table structure changes.
Assuming that PCO_NUMBER is the primary key (change the WHERE clause within the EXISTS statement, depending on the results of the above), the query should be the following:
INSERT INTO PAYMENT_COMMON
(
PCO_NUMBER,
ID_BUILD,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT
)
SELECT
PCO_NUMBER,
ID_BUILD,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT
FROM PAYMENT_COMMON1 C1
WHERE NOT EXISTS (
SELECT 1
FROM PAYMENT_COMMON C
WHERE C.PCO_NUMBER = C1.PCO_NUMBER
);
answered Nov 14 '18 at 3:08
bruceskyausbruceskyaus
316210
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53291304%2fpk-violated-when-i-insert-values-into-a-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You should check which column(s) make up the primary key index (PK_PAYMENT_COMMON). Do this by the following statement:
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'PAYMENT_COMMON'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
But the main problem with your INSERT statement is that rows already exist in the PAYMENT_COMMON table, so don't need to insert them again.
Also, it is better to be explicit than implicit when performing DML statements (INSERT, UPDATE or DELETE). Therefore, avoid SELECT * and specify all of the columns. This helps to avoid unexpected errors if the table structure changes.
Assuming that PCO_NUMBER is the primary key (change the WHERE clause within the EXISTS statement, depending on the results of the above), the query should be the following:
INSERT INTO PAYMENT_COMMON
(
PCO_NUMBER,
ID_BUILD,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT
)
SELECT
PCO_NUMBER,
ID_BUILD,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT
FROM PAYMENT_COMMON1 C1
WHERE NOT EXISTS (
SELECT 1
FROM PAYMENT_COMMON C
WHERE C.PCO_NUMBER = C1.PCO_NUMBER
);
answered Nov 14 '18 at 3:08
bruceskyausbruceskyaus
316210
add a comment |
You should check which column(s) make up the primary key index (PK_PAYMENT_COMMON). Do this by the following statement:
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'PAYMENT_COMMON'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
But the main problem with your INSERT statement is that rows already exist in the PAYMENT_COMMON table, so don't need to insert them again.
Also, it is better to be explicit than implicit when performing DML statements (INSERT, UPDATE or DELETE). Therefore, avoid SELECT * and specify all of the columns. This helps to avoid unexpected errors if the table structure changes.
Assuming that PCO_NUMBER is the primary key (change the WHERE clause within the EXISTS statement, depending on the results of the above), the query should be the following:
INSERT INTO PAYMENT_COMMON
(
PCO_NUMBER,
ID_BUILD,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT
)
SELECT
PCO_NUMBER,
ID_BUILD,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT
FROM PAYMENT_COMMON1 C1
WHERE NOT EXISTS (
SELECT 1
FROM PAYMENT_COMMON C
WHERE C.PCO_NUMBER = C1.PCO_NUMBER
);
answered Nov 14 '18 at 3:08
bruceskyausbruceskyaus
316210
add a comment |
You should check which column(s) make up the primary key index (PK_PAYMENT_COMMON). Do this by the following statement:
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'PAYMENT_COMMON'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
But the main problem with your INSERT statement is that rows already exist in the PAYMENT_COMMON table, so don't need to insert them again.
Also, it is better to be explicit than implicit when performing DML statements (INSERT, UPDATE or DELETE). Therefore, avoid SELECT * and specify all of the columns. This helps to avoid unexpected errors if the table structure changes.
Assuming that PCO_NUMBER is the primary key (change the WHERE clause within the EXISTS statement, depending on the results of the above), the query should be the following:
INSERT INTO PAYMENT_COMMON
(
PCO_NUMBER,
ID_BUILD,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT
)
SELECT
PCO_NUMBER,
ID_BUILD,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT
FROM PAYMENT_COMMON1 C1
WHERE NOT EXISTS (
SELECT 1
FROM PAYMENT_COMMON C
WHERE C.PCO_NUMBER = C1.PCO_NUMBER
);
answered Nov 14 '18 at 3:08
bruceskyausbruceskyaus
316210
You should check which column(s) make up the primary key index (PK_PAYMENT_COMMON). Do this by the following statement:
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'PAYMENT_COMMON'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
But the main problem with your INSERT statement is that rows already exist in the PAYMENT_COMMON table, so don't need to insert them again.
Also, it is better to be explicit than implicit when performing DML statements (INSERT, UPDATE or DELETE). Therefore, avoid SELECT * and specify all of the columns. This helps to avoid unexpected errors if the table structure changes.
Assuming that PCO_NUMBER is the primary key (change the WHERE clause within the EXISTS statement, depending on the results of the above), the query should be the following:
INSERT INTO PAYMENT_COMMON
(
PCO_NUMBER,
ID_BUILD,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT
)
SELECT
PCO_NUMBER,
ID_BUILD,
NUM_DEPARTMENT,
PGC_DATE_CANCELATION,
PGC_AMMOUNT_PAY,
ID_VIA_PAYMENT
FROM PAYMENT_COMMON1 C1
WHERE NOT EXISTS (
SELECT 1
FROM PAYMENT_COMMON C
WHERE C.PCO_NUMBER = C1.PCO_NUMBER
);
answered Nov 14 '18 at 3:08
bruceskyausbruceskyaus
316210
edited Nov 14 '18 at 3:14
answered Nov 14 '18 at 3:08
bruceskyausbruceskyaus
316210
answered Nov 14 '18 at 3:08
bruceskyausbruceskyaus
316210
answered Nov 14 '18 at 3:08
bruceskyausbruceskyaus
316210
316210
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53291304%2fpk-violated-when-i-insert-values-into-a-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Possible duplicate of Constraint Violation Exception ORA-00001
– ljeabmreosn
Nov 14 '18 at 0:25