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 Go to next message
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 #628494 is a reply to message #628490] Tue, 25 November 2014 12:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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

I don't understand.
post CREATE TABLE statement & INSERT statements for sample data; then explain requirement using provided data rows.

> FROM vld_rows_temporary);
almost without any exception, Oracle does not require any "temporary" table to manipulate data
Re: INSERT with SELECT ON DISTINCT [message #628496 is a reply to message #628494] Tue, 25 November 2014 12:26 Go to previous messageGo to next message
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 #628497 is a reply to message #628496] Tue, 25 November 2014 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>sample insert, if you notice out of 3, 2 rows have same value combination for those 2 columns, should pick only 1 rows.
how to decide which row to return & which row to not return?

You could always DELETE "duplicate" row before issuing the SELECT!
Re: INSERT with SELECT ON DISTINCT [message #628500 is a reply to message #628496] Tue, 25 November 2014 12:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you do not care which row you keep (which would be rather odd) you could use the ERROR LOGGING clause of INSERT to reject the duplicates
.

--update:
not ERROR LOGGING, should be LOG ERRORS INTO

[Updated on: Tue, 25 November 2014 12:46]

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 Go to previous message
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.
Previous Topic: Merge materialized views
Next Topic: Need data from table but there is multiple conditions
Goto Forum:
  


Current Time: Thu Mar 28 05:33:28 CDT 2024