How can I delete historical or groups of data from ExpressMaintenance?

Answer: Occasionally, a user will ask how to delete large groups of data from the ExpressMaintenance database.  This tech bulletin will explain how to accomplish a bulk delete.  However, we have intentionally not added such an option to the application for several reasons:

  1. Deleting data does not typically provide a performance improvement because SQL efficiently handles queries anyway.  A better alternative for weak performance might be to add indexes, rebuild all indexes and shrink / repair the database, more memory, faster server, etc.
  2. The option would be dangerous and users could delete data unintentionally.
  3. Bulk deletes should be done by the administrator for security and data integrity reasons.
  4. We cannot anticipate the logic that might be used for bulk deletes.

For these reasons, we recommend using direct SQL statements to perform bulk deletes or other bulk data manipulation.  While this tech bulletin will provide some sample SQL statements for bulk deletes, you may wish to learn more about SQL syntax by visiting the tech bulletin at the following link: How can I learn more about MS SQL Server?

ExpressMaintenance includes an option to create and run SQL statements from within the program.  This option is found under Utilities / Run SQL Statement.   If you do not have this option, make sure you are running the latest release and that you have been granted permission to use this feature by the system administrator as setup under Admin / User Accounts.

If you own the full version of SQL Server or have another tool for making SQL queries you can use your favorite tool.  In addition we now offer ExpressSQL.  ExpressSQL is powerful database management software that includes backup scheduling and many other features.  To download ExpressSQL – click here.  ExpressSQL allows you to create indexes, rebuild indexes, shrink and repair the database and make other direct database changes.

Using ExpressMaintenance, ExpressSQL or another utility, enter your SQL statement and execute it.

Below is a screen shot of the procedure when using ExpressSQL.  From the SQL Query tab you can make queries, bulk updates as well as deletes of the data.

The following table shows some sample SQL statements with certain where clauses that will allow you to make bulk deletes.

Warning – Once you delete data it will be gone.  It is highly recommended that you first backup your data and that these deletes be done by the system administrator.  We recommend you query the data using the select and where clause first to see what data will be deleted.  If you fail to include the where clause, you will delete all data from the table!

Query Units of a certain category. select * from Units
where CategoryID=’mycategorytype’
Delete Units of a certain category. delete from Units
where CategoryID=’mycategorytype’
Delete Parts of a certain type. delete from Parts
where Type=’mypartstype’
Delete Work Orders preceding a certain date.  You should first delete the details records and then the master records as shown to the right. delete from WoDetail
where WoDetail.WorkOrder in
(select Numbered from WoMaster where WoMaster.CompletedDate<’12/31/2010′)delete from WoMaster
where CompletedDate<’12/31/2005′
Delete Parts purchasing records preceding a certain date.  You should first delete the detail records and then the master records. delete from Puch2
where Purch2.Joining in (select Purch.Joining from Purch
where Purch.TheDate<’12/31/2005′)delete from Purch
where TheDate<’12/31/2005′
Delete Purchase Order records preceding a certain date.  You should first delete the detail records and then the master records. delete from POrders2
where POrders2.Joining in (select POrders.Joining from POrders
where POrders.DateClosed<’12/31/2005′)delete from POrders
where DateClosed<’12/31/2005′
Delete Request records preceding a certain date. delete from Requests
where DateRequested<’12/31/2005′

The SQL query language is very powerful and virtually unlimited in features that allow you to query and manipulate data.  Again, use caution and backup your data first.

If you wish to learn more about the SQL language visit the following link: How can I learn more about MS SQL Server?

The screen shot below reflects an SQL delete statement after being executed.