1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Please help with update statement

Discussion in 'SQL PL/SQL' started by james shallow, May 18, 2017.

  1. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Please help, I am trying to update the column "apf_year" currently holding '18/19' with new value of '13/14' but
    i am getting a unique constraint error. I will be grateful if you can please help look at my code and advice accordingly. Thanks

    Code (SQL):
    MERGE INTO brs_mas_apf_archive t1
    USING
    (
    SELECT * FROM brs_mas_apf_archive t2 WHERE t2.APF_YEAR = '18/19'
    )t2
        ON( t2.APF_STUC = t1.APF_STUC
        AND t2.APF_SEQN = t1.APF_SEQN  )
        --AND t2.APF_YEAR = t1.APF_YEAR)
    WHEN MATCHED THEN UPDATE SET
        t1.APF_YEAR = '13/14';
    Code (SQL):
    Error starting at line : 75 IN command -
    MERGE INTO brs_mas_apf_archive a
    USING (SELECT APF_STUC, APF_SEQN,APF_YEAR FROM brs_mas_apf_archive b WHERE apf_year = '18/19')t
    ON (t.APF_STUC = a.APF_STUC
    AND t.APF_SEQN = a.APF_SEQN)
    --and t.APF_YEAR = a.APF_YEAR)
    WHEN MATCHED THEN UPDATE SET a.apf_year = '13/14'
    Error report -
    SQL Error: ORA-00001: UNIQUE CONSTRAINT (BRS.BRS_MAS_APF_ARCHIVE_PK) 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.
    DDL for the table

    CREATE TABLE "BRS"."BRS_MAS_APF_ARCHIVE"
    ( "APF_STUC" VARCHAR2(12),
    "APF_SEQN" VARCHAR2(2),
    "APF_UCID" VARCHAR2(10),
    "APF_UC12" VARCHAR2(10),
    "APF_RECD" DATE,
    "APF_UFPC" VARCHAR2(2),
    "APF_UHOC" VARCHAR2(1),
    "APF_URCC" VARCHAR2(1),
    "APF_CRCF" VARCHAR2(1),
    "APF_CYCL" VARCHAR2(4),
    "APF_YEAR" VARCHAR2(6),
    "APF_WTRD" DATE,
    "APF_RELD" DATE,
    "APF_APID" VARCHAR2(10),
    "APF_AABS" VARCHAR2(1),
    "APF_UDF3" NVARCHAR2(15),
    "APF_UDF4" VARCHAR2(15),
    "APF_ADDED_DATE" DATE,
    CONSTRAINT "BRS_MAS_APF_ARCHIVE_PK" PRIMARY KEY ("APF_STUC", "APF_SEQN", "APF_YEAR")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 10240 NEXT 16570368 MINEXTENTS 1 MAXEXTENTS 121
    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "MAIN" ENABLE
    ) SEGMENT CREATION IMMEDIATE
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS LOGGING
    STORAGE(INITIAL 10240 NEXT 37285888 MINEXTENTS 1 MAXEXTENTS 121
    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "MAIN" "

    Code (SQL):
    sample DATA ON the COLUMN TO be updated

     
    18/19
    18/19
    18/19
    18/19
    18/19
    18/19
    18/19
    18/19
    18/19
    18/19
    18/19
    18/19
    18/19
    13/14
    18/19
    18/19
    15/16
    13/14
    18/19
    18/19
    18/19
    18/19
    18/19
     
    Last edited: May 18, 2017
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,566
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This explains your situation:

    *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."


    You're using Trusted Oracle which has different operating characteristics than 'normal', plain old Oracle. Since I don't have that to 'play' with you may need to search the web for possible solutions; I doubt anyone in this forum is using Trusted Oracle other than yourself.
     
    james shallow likes this.