Skip to content

Deleting Expired and Orphaned Pages

 

Here are the scripts you need to:

  • Delete Paper Prices that have expired. (Delete Expired Paper Prices.sql)
  • Delete Papers that no longer have prices. (Delete Orphan Units.sql)

How to use these scripts

Before running the scripts, please ensure that you first make a backup of the database and update all the prices from the internet. Paper prices that are not updated (ie. Papers that are discontinued like Dukuza and Avalon) can then be deleted using the above scripts.

The first script requires you to enter the ‘MATCATID’ of the Paper Category - otherwise Ink and Plate prices will also be deleted. This would in most cases be ‘1’, but to be sure you can run the following script:

select MATCATID, NAME from MATCAT;

1. Delete Expired Paper Prices.sql

delete from MATPRICE
where MATPRICEID in
(select
MP.MATPRICEID
from MATPRICE MP
left join MATUNIT MU on MP.MATUNITID = MU.MATUNITID
where MU.MATCATID = 1
and MP.UPDATED < (select (CURRENT_DATE - 30) from RDB$DATABASE));
commit work;

2. Delete Orphan Units.sql

Delete from MATUNIT MU
where (select count(*) from MATPRICE MP where MP.MATUNITID = MU.MATUNITID) = 0;
commit work;