Home » SQL & PL/SQL » SQL & PL/SQL » INSERT with SELECT ON DISTINCT (oracle 11g Rel2)
INSERT with SELECT ON DISTINCT [message #628490] |
Tue, 25 November 2014 12:04 |
azeem87
Messages: 116 Registered: September 2005 Location: dallas
|
Senior Member |
|
|
Hi,
Need a help in correcting this syntax please,
i have a unique index/constraint on a composite 2 columns beneid_benebk_id, which i want to put in select clause of insert,
it's gives missing expression error, what should be the right way to get ouput with unique of those 2 columns
INSERT INTO vld_rows (VLD_BENE_SEQ_NB,
BENE_ID_TYPE_CD,
BENEBNK_ID_TYPE_CD,
BENE_ID,
BENEBNK_ID,
CRE_BY_USR_ID,
CRE_DT,
LAST_MOD_BY_USR_ID,
LAST_MOD_DT)
(SELECT VLD_BENE_SEQ_NB,
BENE_ID_TYPE_CD,
BENEBNK_ID_TYPE_CD,
DISTINCT BENE_ID, --> these 2 column combination i want unique, it's duplicate, we want only unique.
DISTINCT BENEBNK_ID, --> these 2 column combination i want unique, it's duplicate, we want only unique
CRE_BY_USR_ID,
CRE_DT,
LAST_MOD_BY_USR_ID,
LAST_MOD_DT
FROM vld_rows_temporary);
Thanks
|
|
|
|
Re: INSERT with SELECT ON DISTINCT [message #628496 is a reply to message #628494] |
Tue, 25 November 2014 12:26 |
azeem87
Messages: 116 Registered: September 2005 Location: dallas
|
Senior Member |
|
|
sorry for not giving all info,
source table and target table have same structure except that unique constraint on combination of 2 colums, as this is loaded with data from a file that has duplicate value for this column combination
CREATE TABLE VLD_ROWS(
VLD_BENE_SEQ_NB NUMBER(15) NOT NULL,
BENE_ID_TYPE_CD VARCHAR2(10 BYTE),
BENE_ID VARCHAR2(35 CHAR),
BENEBNK_ID_TYPE_CD VARCHAR2(10 BYTE),
BENEBNK_ID VARCHAR2(35 CHAR),
CRE_BY_USR_ID VARCHAR2(30 BYTE) NOT NULL,
CRE_DT DATE NOT NULL,
LAST_MOD_BY_USR_ID VARCHAR2(30 BYTE),
LAST_MOD_DT DATE
);
CREATE UNIQUE INDEX VLD_rows_AK ON VLD_rows (BENE_ID, BENEBNK_ID);;
CREATE UNIQUE INDEX VLD_rows_PK ON VLD_rows(VLD_BENE_SEQ_NB);
ALTER TABLE VLD_rows ADD ( CONSTRAINT VLD_rows_PK PRIMARY KEY
(VLD_rows_SEQ_NB) USING INDEX VLD_rows_PK);
sample insert, if you notice out of 3, 2 rows have same value combination for those 2 columns, should pick only 1 rows.
SET DEFINE OFF;
Insert into VLD_rows_TEMPORARY
(VLD_BENE_SEQ_NB, BENE_ID_TYPE_CD, BENE_ID, BENEBNK_ID_TYPE_CD, BENEBNK_ID, CRE_BY_USR_ID, CRE_DT)
Values
(36698, 'ACCOUNT', '123803773', 'RCVBA', 'CBMBU33',
'SYSTEM', TO_DATE('11/25/2014 12:28:33', 'MM/DD/YYYY HH24:MI:SS'));
Insert into VLD_rows_TEMPORARY
(VLD_BENE_SEQ_NB, BENE_ID_TYPE_CD, BENE_ID, BENEBNK_ID_TYPE_CD, BENEBNK_ID, CRE_BY_USR_ID, CRE_DT)
Values
(36498, 'ACCOUNT', '123803773', 'TWWER', 'CBMBU33',
'SYSTEM', TO_DATE('11/25/2014 12:28:33', 'MM/DD/YYYY HH24:MI:SS'));
Insert into VLD_rows_TEMPORARY
(VLD_BENE_SEQ_NB, BENE_ID_TYPE_CD, BENE_ID, BENEBNK_ID_TYPE_CD, BENEBNK_ID, CRE_BY_USR_ID, CRE_DT)
Values
(35498, 'ACCOUNT', '342526666', 'TWWER', 'RMSOTSA',
'SYSTEM', TO_DATE('11/25/2014 12:28:33', 'MM/DD/YYYY HH24:MI:SS'));
commit;
[Updated on: Tue, 25 November 2014 12:27] Report message to a moderator
|
|
|
|
|
Re: INSERT with SELECT ON DISTINCT [message #628503 is a reply to message #628496] |
Tue, 25 November 2014 13:36 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
azeem87 wrote on Tue, 25 November 2014 13:26
CREATE UNIQUE INDEX VLD_rows_PK ON VLD_rows(VLD_BENE_SEQ_NB);
ALTER TABLE VLD_rows ADD ( CONSTRAINT VLD_rows_PK PRIMARY KEY
(VLD_rows_SEQ_NB) USING INDEX VLD_rows_PK);
That index should be created WITHOUT being unique so that you can disable the constraint when need be.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 05:33:28 CDT 2024
|