0

My objective is to build a master Serial No table from many different tables and each Serial No must be unique.

 CREATE TABLE  "TBL_SERIAL_NUMBER_MASTER" 
   (    "INTERNAL_RECORD_ID" VARCHAR2(60) NOT NULL ENABLE, 
    "ASSET_ID" VARCHAR2(60), 
    "SERIAL_NUMBER" VARCHAR2(1000) NOT NULL ENABLE, 
    "VALID" VARCHAR2(60) DEFAULT 'Valid', 
    "HOST_NAME" VARCHAR2(255), 
     CHECK ( valid IN ('Invalid', 'Valid')) ENABLE, 
     CHECK ( valid IN ('Invalid', 'Valid')) ENABLE, 
     CHECK ( valid IN ('Invalid', 'Valid')) ENABLE, 
     CONSTRAINT "TBL_SERIAL_NUMBER_MASTER_CON" PRIMARY KEY ("SERIAL_NUMBER") ENABLE
   ) ;


CREATE OR REPLACE TRIGGER  "TBL_SERIAL_NUMBER_MASTER_INTER" BEFORE
  INSERT
    ON tbl_serial_number_master FOR EACH ROW  WHEN (
      NEW.internal_record_id IS NULL
    ) BEGIN :NEW.internal_record_id := tbl_serial_number_master_inter.NEXTVAL;
END;
/
ALTER TRIGGER  "TBL_SERIAL_NUMBER_MASTER_INTER" ENABLE;

I have already updated the SERIAL_NUMBER field using one table. Now I am trying to add more Serial Nos from another table. These other tables have duplicate and null value Serial Nos. Can you please advise how I can create the query to add unique Serial Nos that are already not in the TBL_SERIAL_NUMBER_MASTER.

Please assume following fields for the other table.

TABLE_SOURCE_B

  1. ID :-PK
  2. SERIAL_NUMBER
  3. List item

Name

Dilan Perera
  • 23
  • 4
  • do you want to add only those rows to master table which are not causing duplication of serial_number in Master table or do you want to replace serial_number with unique one while adding to Master table? – Harshil Doshi Sep 19 '16 at 03:09
  • I want to add only those rows to master table which are not causing duplication of serial_number in Master table – Dilan Perera Sep 19 '16 at 03:30
  • See link http://stackoverflow.com/questions/14649622/oracle-insert-into-select-dup-val-on-index-exception-behavior – Evgeniy K. Sep 19 '16 at 04:00

1 Answers1

0

In your Master Table, 'Internal_Record_Id' has Not Null constraint. So I am inserting values of ID field of Source table into 'internal_record_id'. Try the following query.

 INSERT INTO 
    TBL_SERIAL_NUMBER_MASTER (INTERNAL_RECORD_ID,SERIAL_NUMBER) 
 SELECT  ID,SERIAL_NUMBER 
    FROM TABLE_SOURCE_B B 
       WHERE B.SERIAL_NUMBER NOT IN(SELECT SERIAL_NUMBER 
                                              FROM TBL_SERIAL_NUMBER_MASTER);

Hope it helps!

Harshil Doshi
  • 3,497
  • 3
  • 14
  • 37
  • INSERT INTO TBL_SERIAL_NUMBER_MASTER (SERIAL_NUMBER, VALID) SELECT DISTINCT (SERIAL_NUMBER), 'Valid' FROM TBL_DEVICE_LOCALITY WHERE SERIAL_NUMBER IS NOT NULL Error report - unique constraint (TBL_SERIAL_NUMBER_MASTER_CON) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key. – Dilan Perera Sep 19 '16 at 05:15
  • good. this is working for me. however i noticed i can only update 2 coloumns. which looks like a limitation in the Oracle 11g i am using. – Dilan Perera Oct 25 '16 at 05:34