How to Delete or Erase All Patients and Patient Data

Owned by Joaquin Blaya
Last updated: Apr 28, 2016 by Becky SmithVersion comment
3 min read

What this page is for

These are the instructions for OpenMRS 1.6, OpenMRS 1.8.3, OpenMRS 1.9.8 and OpenMRS 2.1. They cover how to delete all of the patient data from OpenMRS while keeping all of the forms, users, and settings that you've made (i.e. this is how to clear or delete all patient information but keeping everything else).  It is also possible to do this by only exporting the tables that you want using mysqldump, but this is not covered here.  

Documentation / How-To

To do this, you should log into your mysql database using a user that has delete privileges (best option is probably as an administrator).

For OpenMRS 1.6, paste the following queries in and you're done.

delete from obs;
delete from accesslogging_accesslog;
delete from encounter;
delete from patient_identifier;
delete from cohort_member;
delete from usagestatistics_usage;
delete from call_log;
delete from patient_program;
delete from sms_history;
delete from patient;
delete from person_address where person_id not in (select person_id from users);
delete from person_attribute where person_id not in (select person_id from users);
delete from person_name where person_id not in (select person_id from users);
delete from relationship;
delete from person where person_id not in (select person_id from users);

For OpenMRS 1.8.3, the command would need to take foreign keys into consideration and would look like this:

SET foreign_key_checks = 0;
delete from obs;
delete from accesslogging_accesslog;
delete from encounter;
delete from patient_identifier;
delete from cohort_member;
delete from usagestatistics_usage;
delete from call_log;
delete from patient_program;
delete from sms_history;
delete from patient;
delete from person_address where person_id not in (select person_id from users);
delete from person_attribute where person_id not in (select person_id from users);
delete from person_name where person_id not in (select person_id from users);
delete from relationship;
delete from person where person_id not in (select person_id from users);
delete from alert_archive;
delete from alert_error;

SET foreign_key_checks = 1;

Important Side Note: The first command disables foreign keys to allow deletion of the pt(patient tables) data. The second command deletes the pt data, but not user data, and the third command enables foreign keys again.

For OpenMRS 1.9.8, the command would need to take providers and visits into consideration and would look like this:

SET foreign_key_checks = 0; 
TRUNCATE TABLE obs; 
TRUNCATE TABLE encounter; 
TRUNCATE TABLE accesslogging_accesslog;
TRUNCATE TABLE patient_identifier; 
TRUNCATE TABLE cohort_member; 
TRUNCATE TABLE usagestatistics_usage;
TRUNCATE TABLE call_log;
TRUNCATE TABLE patient_program; 
TRUNCATE TABLE sms_history;
TRUNCATE TABLE patient; 
delete from person_address where person_id not in (select person_id from users) AND person_id not in (SELECT person_id FROM provider); 
delete from person_attribute where person_id not in (select person_id from users) AND person_id not in (SELECT person_id FROM provider); 
delete from person_name where person_id not in (select person_id from users) AND person_id not in (SELECT person_id FROM provider); 
delete from person where person_id not in (select person_id from users) AND person_id not in (SELECT person_id FROM provider); 
TRUNCATE TABLE relationship; delete from person where person_id not in (select person_id from users) AND person_id not in (SELECT person_id FROM provider); 
TRUNCATE TABLE alert_archive; 
TRUNCATE TABLE alert_error; 
TRUNCATE TABLE visit; 
TRUNCATE TABLE visit_attribute; 
TRUNCATE TABLE cashier_bill; 
TRUNCATE TABLE cashier_bill_line_item; 
TRUNCATE TABLE cashier_bill_payment; 
TRUNCATE TABLE cashier_bill_payment_attribute; 
TRUNCATE TABLE active_list; 
TRUNCATE TABLE encounter_provider; 
TRUNCATE TABLE idgen_log_entry; 
TRUNCATE TABLE idgen_pooled_identifier; 
TRUNCATE TABLE note; 
TRUNCATE TABLE orders; 
TRUNCATE TABLE patient_state; 
TRUNCATE TABLE person_merge_log; 
TRUNCATE TABLE test_order; 
TRUNCATE TABLE xforms_person_repeat_attribute; 
SET foreign_key_checks = 1;

Important Side Note: You can still use the 'delete from' syntax as previously, but 'TRUNCATE' is preferred which resets the auto-increment counters to 1.   Any commands dealing with tables that you don't have can be omitted; (for example tables added by modules you don't use).  You may have other tables added by modules that should also be cleared.

For OpenMRS 2.1, the command would need to take allergies into consideration and would look like this:

SET foreign_key_checks = 0; 
TRUNCATE TABLE obs; 
TRUNCATE TABLE encounter; 
TRUNCATE TABLE accesslogging_accesslog;
TRUNCATE TABLE patient_identifier; 
TRUNCATE TABLE cohort_member; 
TRUNCATE TABLE usagestatistics_usage;
TRUNCATE TABLE call_log;
TRUNCATE TABLE patient_program; 
TRUNCATE TABLE sms_history;
TRUNCATE TABLE patient; 
delete from person_address where person_id not in (select person_id from users) AND person_id not in (SELECT person_id FROM provider); 
delete from person_attribute where person_id not in (select person_id from users) AND person_id not in (SELECT person_id FROM provider); 
delete from person_name where person_id not in (select person_id from users) AND person_id not in (SELECT person_id FROM provider); 
delete from person where person_id not in (select person_id from users) AND person_id not in (SELECT person_id FROM provider); 
TRUNCATE TABLE relationship; delete from person where person_id not in (select person_id from users) AND person_id not in (SELECT person_id FROM provider); 
TRUNCATE TABLE alert_archive; 
TRUNCATE TABLE alert_error; 
TRUNCATE TABLE visit; 
TRUNCATE TABLE visit_attribute; 
TRUNCATE TABLE cashier_bill; 
TRUNCATE TABLE cashier_bill_line_item; 
TRUNCATE TABLE cashier_bill_payment; 
TRUNCATE TABLE cashier_bill_payment_attribute; 
TRUNCATE TABLE active_list; 
TRUNCATE TABLE encounter_provider; 
TRUNCATE TABLE idgen_log_entry; 
TRUNCATE TABLE idgen_pooled_identifier; 
TRUNCATE TABLE note; 
TRUNCATE TABLE orders; 
TRUNCATE TABLE patient_state; 
TRUNCATE TABLE person_merge_log; 
TRUNCATE TABLE test_order; 
TRUNCATE TABLE xforms_person_repeat_attribute; 
TRUNCATE TABLE allergy;
TRUNCATE TABLE allergy_attribute;
SET foreign_key_checks = 1;