Delete Records
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- SQL to delete Terminal ID Manager operational data ---<<)) |
-- | |
-- |>===========================================================================================================<|
-- | |
-- | This script contains sample statements to delete all or parts of the operational Pool, |
-- | AssociationSet, and ID data of the Terminal ID Manager's Derby database. |
-- | |
-- | |
-- | This script assist users with deleting data, but does not remove the Pool, AssociationSet, |
-- | or ID tables from the database. |
-- | |
-- | |
-- | Note: |
-- | |
-- | * Names of IDs, Pools, and AssociationSets are case-sensitive. |
-- | |
-- | * Lines beginning with "--" are comments; all others must be valid SQL for Terminal ID Manager. |
-- | |
-- | * Uncomment only the SQL statements to be executed. |
-- | |
-- | * A Pool can be deleted only if it does not contain any IDs. |
-- | |
-- | * To delete an AssociationSet, you must first change the setname of each ID that is a |
-- | member of that AssociationSet to NULL. |
-- | |
-- |>===========================================================================================================<|
-- |>===========================================================================================================<|
-- | |
-- | Do not modify or comment these statements - they obtain a connection to |
-- | the Terminal ID Manager Derby database, called DerbyIDM. |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
SET SCHEMA IDM;
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Statements to delete all ID, Pool, and AssociationSet data ---<<)) |
-- | |
-- | ==> Execute these statements to empty the Terminal ID Manager database tables. |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
-- removes all the ID and Pool and AssociationSet definitions
-- delete from ID;
-- delete from AssociationSet;
-- delete from Pool;
-- |>===========================================================================================================<|
-- | |
-- | (>>--- Statements to remove or delete one or more IDs from Pools and AssociationSets ---<<)) |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
-- delete all the IDs contained in all of the Pools and AssociationSets
-- delete from ID;
-- delete all the IDs from one Pool.
-- delete from ID where poolname = 'Name_Of_Pool_to_Empty';
-- removes all the IDs currently members of an AssociationSet. Note that the IDs are not deleted.
-- update ID set setname = NULL where setname = 'Name_of_AssociationSet_to_empty';
-- moves all the IDs from one AssociationSet that are from a given Pool to a different AssociationSet.
-- update ID set setname = 'To_AssocSet' where setname = 'From_AssociationSet' and poolname = 'Name_Of_Pool';
-- delete all the IDs with a name matching a particular pattern where those IDs are from an AssociationSet
-- that has a name matching a particular pattern and are defined in a Pool whose name has a particular pattern.
-- For example: this statement might delete IDs 'myTest1234' and 'newTest5678' if they are defined in Pool 'FinalTest'
-- and are members of the AssociationSet 'TestPayroll'.
-- delete from ID where idname like '%Test%' and setname like 'TEST%' and poolname like '%Test';
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Delete one or more Pool definitions ---<<)) |
-- | |
-- | ==> Pool must not contain any IDs for delete to succeed |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
-- removes all the Pool definitions from Terminal ID Manager. Deleting all IDs empties all Pools so they can be deleted.
-- delete from ID;
-- delete from Pool;
-- remove one Pool definition from Terminal ID Manager. Name of Pool is case-sensitive. Pool must be empty before deleting.
-- delete from ID where poolname = 'Name_Of_Pool_To_Delete';
-- delete from Pool where poolname = 'Name_Of_Pool_To_Delete';
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Delete one or more Association Set definitions ---<<)) |
-- | |
-- | ==> Remove all IDs from an AssociationSet before the AssociationSet is deleted. An ID is |
-- | removed from an AssociationSet by setting the 'setname' field in the ID to NULL. |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
-- removes all the AssociationSet definitions from Terminal ID Manager
-- update ID set setname = NULL where setname IS NOT NULL;
-- delete from AssociationSet;
-- removes one AssociationSet definition from Terminal ID Manager. Remove the IDs that are members of the AssociationSet first.
-- update ID set setname = NULL where setname = 'Name_Of_AssociationSet_To_Delete';
-- delete from AssociationSet where setname = 'Name_Of_AssociationSet_To_Delete';
-- remove all AssociationSets with names matching a pattern. Removes IDs from those AssociationSets first but
-- does not delete them.
-- example: will remove IDs from set membership, and then delete AssociationSets 'ABCzzzz123', 'zzzzXYZ', and '123zzzz'.
-- update ID set setname = NULL where setname like '%zzzz%';
-- delete from AssociationSet where setname like '%zzzz%';
-- |>===========================================================================================================<|
-- | Always the final statement - do not Modify or comment. This terminates the 'ij' command processor |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
EXIT;