• 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