OpenClinica Study Cleanup SQLs

This page explains how to clean up the study before syncing data from OC freshly.

To delete the existing visits in the CP.

Click to expand

 Delete from os_oc_visits where subject_id in 
      (select identifier from os_oc_subjects where study_id in 
        (select identifier from os_oc_studies where cp_id = ?))

cp_id will be the collection protocol identifier to which the visits belong.

To delete the existing patients in the CP.

Click to expand

delete from os_oc_subjects where study_id in 
        (select identifier from os_oc_studies where cp_id = ?)

cp_id will be the collection protocol identifier to which the patients belong.

To update the older deleted records name in CRFs table use the below SQL.

Click to expand

update dyextn_containers set deleted_on = now(), name = concat(name, '_deleted_20210225')
where identifier in (select container_id from catissue_form_context where cp_id = ?)

cp_id will be the collection protocol identifier to which the belong.

To delete the CRFs records of existing CRFs use the below SQL.

Click to expand

delete from catissue_form_record_entry where form_ctxt_id in
      (select identifier from catissue_form_context where cp_id = ?)

cp_id will be the collection protocol identifier to which the CRF records belong.

To avoid the duplicate form context id error you will need to delete the existing CRFs. To delete those CRFs you need to delete their context identifiers.

Click to expand

delete from catissue_form_context where cp_id = ?

cp_id will be the collection protocol identifier to which the forms belong.