|
Question:
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:
-
Deleting
data does not provide a performance improvement because SQL
efficiently handles queries.
-
The
option would be dangerous and users could delete data
unintentionally.
-
Bulk
deletes should be done by the administrator for security and data
integrity reasons.
-
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?
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 you can use
the ExpressUtility which is available free from our website. For
information on downloading, installing and setting up ExpressUtility,
please see the tech bulletin on
ExpressUtility.
Once you have
ExpressUtility running and connected to the server you will need to
select the ExpressMaintenance database and then click on the Explore
Data tab.
From the
Explore Data 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.
Type or paste the SQL statement in the query box and then click the
Execute Query button. The results will be show in the center right
section (see screen shot below).
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. |
delete from WoDetail
where WoDetail.WorkOrder in (select Numbered from WoMaster where
WoMaster.CompletedDate<'12/31/2005')
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' |
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.
 |
