[OPEN-ILS-DEV] Removing records - Training Opportunity...

[Brandon W. Uhlman]

Additionally, if you want the records actually removed from the  
PostgreSQL, and not just stored with a deleted flag, you want to wrap  
all of that in a transaction dropping the rule that does bib  
protection, and then re-adding it before committing. Something like  
this:

BEGIN;
DROP RULE protect_bib_rec_delete ON biblio.record_entry;
DELETE FROM ...;
DELETE FROM ...;
DELETE FROM ...;
CREATE RULE protect_bib_rec_delete AS
     ON DELETE TO biblio.record_entry DO INSTEAD  UPDATE  
biblio.record_entry SET deleted = true;
COMMITT;

~B


Quoting James Fournie <jfournie at sitka.bclibraries.ca>:

> Hi Grant,
>
> Looks like you want to use IN instead of =
>
> # DELETE from metabib.metarecord_source_map where source IN (SELECT id
> FROM biblio.record_entry where tcn_value like 'cis%');
>
> ~James Fournie
> BC SITKA Team
>
> On Wed, Oct 22, 2008 at 1:54 PM, Grant Johnson <FGJohnson at upei.ca> wrote:
>> I don't want to change the table constraints in Evergreen.
>> But have a training opportunity for someone. :-)
>>
>> We need to delete some electronic records without items from the   
>> catalog.(for now).
>> These records have a biblio.record_entry.tcn_source of LIKE 'cis'.
>>
>> I can't delete from biblio.record_entry as it's referenced as a   
>> foreign_key in .metarecord.
>> I can't delete from .metarecord as it's referenced as a foreign key  
>>  in .metarecord_source_map.
>> I CAN remove the rows one at a time from the msm -> m -> re and the  
>>  record is gone.
>>
>> Can someone help with the syntax on how to batch delete the   
>> following..... and let me know if this is all wrong!
>>
>> I know what this means - and am looking up how to build the syntax tonight:
>>  - ERROR:  more than one row returned by a subquery used as an   
>> expression... :-)
>>
>> # DELETE from metabib.metarecord_source_map  where source = (SELECT  
>>  id FROM biblio.record_entry where tcn_value like 'cis%');
>> # DELETE from metabib.metarecord where master_record = ((SELECT id   
>> FROM biblio.record_entry where tcn_value like 'cis%');
>> # DELETE from biblio.record_entry where tcn_value like 'cis%');
>>
>>
>> Table References:
>> biblio.record_entry
>> id = 14497
>>
>> metabib.metarecord
>> id=17448     master_record=14497
>>
>> metaib.metarecord_source_map
>> id=14710      metarecord=17448    source=14497
>> --
>>
>> F. Grant Johnson
>>  Systems Coordinator
>>  Robertson Library
>>  University of Prince Edward Island
>>
>>
>



======================================
Brandon W. Uhlman, Systems Consultant
Public Library Services Branch
British Columbia Ministry of Education
Vancouver, BC (and Lillooet, BC)

Phone: (604) 660-2972 or (250) 256-0344
E-mail: brandon.uhlman at gov.bc.ca
         brandon.uhlman at bclibrary.ca