Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    SQL to change sublibrary in z305 records gets unique constraint error

    • Article Type: General
    • Product: Aleph
    • Product Version: 20

    Problem Symptoms:
    SQL to change sublibrary in z305 ("PATRON?€™S LOCAL INFORMATION") records from BBBBB to AAAAA:

    SQL> UPDATE Z305
    SET Z305_REC_KEY = substr(Z305_REC_KEY,1,12) || 'AAAAA'
    where substr(Z305_REC_KEY,13,5) = 'BBBBB';

    gets unique constraint error:

    ERROR at line 1:
    ORA-00001: unique constraint (XXX50.Z305_ID) violated

    Cause:
    SQL lacks check for existing AAAAA record.

    Resolution:
    Change the SQL to the following:

    UPDATE Z305
    SET Z305_REC_KEY = substr(Z305_REC_KEY,1,12) || 'AAAAA'
    where substr(Z305_REC_KEY,13,5) = 'BBBBB'
    and substr(Z305_REC_KEY,1,12) not in
    (select substr (Z305_REC_KEY,1,12) from z305 where substr (Z305_REC_KEY,13,5) = 'AAAAA');

    Category: System Management (500)

    Subject: Oracle (500)


    • Article last edited: 10/8/2013