ALTER TABLE actor.usr_address DISABLE TRIGGER audit_actor_usr_address_update_trigger;
ALTER TABLE actor.usr DISABLE TRIGGER audit_actor_usr_update_trigger;
+ALTER TABLE actor.usr_message DISABLE RULE protect_usr_message_delete;
CREATE INDEX tmp_addr_replaces ON actor.usr_address(replaces);
BEGIN;
UPDATE actor.usr SET mailing_address = NULL, billing_address = NULL
WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.usr_message WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
COMMIT;
DROP INDEX actor.tmp_addr_replaces;
ALTER TABLE actor.usr_address ENABLE TRIGGER audit_actor_usr_address_update_trigger;
ALTER TABLE actor.usr ENABLE TRIGGER audit_actor_usr_update_trigger;
+ALTER TABLE actor.usr_message ENABLE RULE protect_usr_message_delete;
-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
\set ou_to_del ''''EXAMPLE''''
+\set vol_del_table ORGUNIT_volume_bibs
\set ECHO all
\timing
+DROP TABLE IF EXISTS esi.:vol_del_table;
ALTER TABLE asset.call_number DISABLE RULE protect_cn_delete;
ALTER TABLE asset.call_number DISABLE TRIGGER audit_asset_call_number_update_trigger;
(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
);
-CREATE TABLE esi.albemarle_volume_bibs AS SELECT DISTINCT record
+CREATE TABLE esi.:vol_del_table AS SELECT DISTINCT record
FROM asset.call_number WHERE owning_lib IN
(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
ALTER TABLE asset.call_number ENABLE RULE protect_cn_delete;
ALTER TABLE asset.call_number ENABLE TRIGGER audit_asset_call_number_update_trigger;
-CREATE INDEX alb_vol_bib_idx ON esi.albemarle_volume_bibs(record);
+CREATE INDEX org_vol_bib_idx ON esi.:vol_del_table(record);
-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
\set ou_to_del ''''EXAMPLE''''
+\set vol_del_table ORGUNIT_volume_bibs
\set ECHO all
\timing
DELETE FROM authority.bib_linking WHERE bib IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
);
DELETE FROM metabib.browse_entry_def_map WHERE source IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
);
DELETE FROM acq.lineitem WHERE eg_bib_id IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
);
DELETE FROM biblio.monograph_part WHERE record IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
);
DELETE FROM biblio.peer_bib_copy_map WHERE peer_record IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
);
DELETE FROM acq.lineitem WHERE queued_record IN (
SELECT id FROM vandelay.queued_bib_record WHERE imported_as IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
)
);
DELETE FROM acq.acq_lineitem_history WHERE queued_record IN (
SELECT id FROM vandelay.queued_bib_record WHERE imported_as IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
)
);
DELETE FROM vandelay.queued_bib_record WHERE imported_as IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
);
DELETE FROM metabib.record_attr_vector_list WHERE source IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
);
DELETE FROM metabib.record_sorter WHERE source IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
);
DELETE FROM booking.resource_type WHERE record IN
(
- SELECT record FROM esi.albemarle_volume_bibs x
+ SELECT record FROM esi.:vol_del_table x
WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
);