How to get rid of work items which have not been completed.

Document ID : KB000012498
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

This question was asked in a context when Client started with a new cleaned Task Persistence Database and did no more want the remaining work items in the lists.

Note: SQL statements are given for MS SQL server.

Question:

How to delete work list items which have not been completed and dealing with the referential constraints?

Answer:

Possible foreign keys can prevent us to simply delete WP_WORK_ITEM rows. 

The referential path is the following: 

WP_WORK_ITEM <--(fk1_wp_wi_alert) <-- WP_WI_ALERT <-- (fk1_wp_wi_alert_act) <-- WP_WI_ALERT_ACT 

 

To avoid this referential constraint conflict run the 3 following statements in this order: 

 

DELETE child2 FROM [WP_WI_ALERT_ACT] AS child2 

INNER JOIN [WP_WI_ALERT] AS child1 ON child2.WI_ALERT_ID = child1.WI_ALERT_ID AND 

child2.WI_ALERT_DB=child1.WI_ALERT_DB 

INNER JOIN [WP_WORK_ITEM] AS parent ON child1.ACTI_ID = parent.ACTI_ID AND 

child1.ACTI_DB = parent.ACTI_DB AND 

child1.WI_ITERATION=parent.WI_ITERATION 

WHERE parent.COMPLETE_DATE is null; 

 

DELETE child FROM [WP_WI_ALERT] AS child 

INNER JOIN [WP_WORK_ITEM] AS parent ON child.ACTI_ID = parent.ACTI_ID AND 

child.ACTI_DB = parent.ACTI_DB AND 

child.WI_ITERATION=parent.WI_ITERATION 

WHERE parent.COMPLETE_DATE is null; 

 

DELETE FROM [WP_WORK_ITEM] WHERE COMPLETE_DATE is null;