Answers.Body: <p>While there's a good answer as indicated by the comments above, in this specific issue might be seeing data from the cache in those autocomplete results?</p>
  
  <p>CiviCRM has a UI to clear the cache at something like <strong>Administer > System Settings > Cleanup Caches &amp; Update Paths</strong></p>
  
  <p><em>Using CiviCRM's public interfaces / ux / tools like <code>civix</code> are recommended over direct SQL.</em> But you asked about a specific case (contact table wiped, autocomplete shows retained results), so there you go :)</p>
  
  <p>Your SQL may still not fully anonymize the DB (see other answer), and there's no simple answer which can ensure you do this short of wiping and installing a fresh DB.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-18T19:06:26.400Z
Answers.Text: It's probably also sort_name.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-19T09:38:03.617Z
Answers.Text: Dear Chris, I voted down as although this is sage advice (to use the GUI) it's not an answer. It was the sort_name that Pete and Demerit have mentioned.
Answers.UserDisplayName: Darren
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-22T07:56:20.293Z
Answers.Text: No worries, thanks for saying why :)
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-18T18:55:00.520Z
Answers.LastActivityDate: 2020-03-18T18:55:00.520Z
Answers.UserDisplayName: Chris Burgess
Answers.Body: <p>As people have pointed out there is a very comprehensive answer to the question of anonymising data, however this questions was about simply clearing data.</p>
  
  <p>The correct answer from Pete and Demerit was that I had missed the sort_name column from the civicrm_contact table. So the full SQL is now:-</p>
  
  <pre><code>delete from civicrm_email; 
  delete from civicrm_address; 
  delete from civicrm_phone;
  update civicrm_contact set birth_date = NULL; 
  update civicrm_contact set first_name = 'Staging';
  update civicrm_contact set middle_name = '';   
  update civicrm_contact set last_name = concat('Test',id);
  update civicrm_contact set legal_name = ''; 
  update civicrm_contact set display_name = concat('Staging Test',id); 
  update civicrm_contact set sort_name = concat('Staging, Test',id); 
  update civicrm_contact set addressee_display = 'Dear Staging';
  update civicrm_contact set email_greeting_display = 'Dear Staging';
  update civicrm_contact set postal_greeting_display = 'Dear Staging';
  </code></pre>
  
  <p>And as pointed out by @Chris you will also need to clear the cache tables, but please use the GUI for this as direct SQL on these tables can cripple the CiviCRM caching logic.</p>
  
  <p>Feel free to edit and expand this answer if you know of any other fields that can be cleared by SQL.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-19T09:44:43.060Z
Answers.LastActivityDate: 2020-03-19T09:44:43.060Z
Answers.UserDisplayName: Darren
Body: <p>We have cloned a live DB and run the following SQL to anonymise the contact data:-</p>
  
  <pre><code>delete from civicrm_email; 
  delete from civicrm_address; 
  update civicrm_contact set birth_date = NULL; 
  update civicrm_contact set first_name = 'Staging';
  update civicrm_contact set middle_name = '';   
  update civicrm_contact set last_name = concat('Test',id); 
  update civicrm_contact set legal_name = ''; 
  update civicrm_contact set display_name = concat('Staging Test',id); 
  update civicrm_contact set addressee_display = '';
  </code></pre>
  
  <p>However, when we search for contacts (Search/Find Contacts) it still displays the original contact name under the "Name" column.</p>
  
  <p>How can we improve this SQL script to remove all traces of contact names?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-03-18T17:27:16.917Z
Comments.Text: There's a good answer to this at https://civicrm.stackexchange.com/questions/17898/how-would-you-anonymise-or-redact-private-data-for-developer-stage
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-03-18T17:27:43.917Z
Comments.Text: Does this answer your question? [How would you anonymise (or redact) private data for developer stage?](https://civicrm.stackexchange.com/questions/17898/how-would-you-anonymise-or-redact-private-data-for-developer-stage)
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-03-18T20:18:59.670Z
Comments.Text: You may need to clear out Sort Name too??
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-03-19T09:38:42.533Z
Comments.Text: Thank you @petednz-fuzion I'd missed the sort_name column.
Comments.UserDisplayName: Darren
Tags: <contacts>
Title: How do I clear all the personal / sensitive data from CiviCRM DB via SQL?
ThreadText: # How do I clear all the personal / sensitive data from CiviCRM DB via SQL?
  
  (Asked by: username_0 on 2020-03-18)
  
  We have cloned a live DB and run the following SQL to anonymise the contact data:-
  
  ```
  delete from civicrm_email; 
  delete from civicrm_address; 
  update civicrm_contact set birth_date = NULL; 
  update civicrm_contact set first_name = 'Staging';
  update civicrm_contact set middle_name = '';   
  update civicrm_contact set last_name = concat('Test',id); 
  update civicrm_contact set legal_name = ''; 
  update civicrm_contact set display_name = concat('Staging Test',id); 
  update civicrm_contact set addressee_display = '';
  
  ```
  
  However, when we search for contacts (Search/Find Contacts) it still displays the original contact name under the "Name" column.
  
  How can we improve this SQL script to remove all traces of contact names?
  
  **Tags:** contacts
  
  > **Comments:**
  > **username_1**: There's a good answer to this at https://civicrm.stackexchange.com/questions/17898/how-would-you-anonymise-or-redact-private-data-for-developer-stage  
  > **username_1**: Does this answer your question? [How would you anonymise (or redact) private data for developer stage?](https://civicrm.stackexchange.com/questions/17898/how-would-you-anonymise-or-redact-private-data-for-developer-stage)  
  > **username_2**: You may need to clear out Sort Name too??  
  > **username_0**: Thank you @username_2 I'd missed the sort_name column.  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2020-03-19. Score: 4 | Accepted answer
  
  As people have pointed out there is a very comprehensive answer to the question of anonymising data, however this questions was about simply clearing data.
  
  The correct answer from Pete and username_1 was that I had missed the sort\_name column from the civicrm\_contact table. So the full SQL is now:-
  
  ```
  delete from civicrm_email; 
  delete from civicrm_address; 
  delete from civicrm_phone;
  update civicrm_contact set birth_date = NULL; 
  update civicrm_contact set first_name = 'Staging';
  update civicrm_contact set middle_name = '';   
  update civicrm_contact set last_name = concat('Test',id);
  update civicrm_contact set legal_name = ''; 
  update civicrm_contact set display_name = concat('Staging Test',id); 
  update civicrm_contact set sort_name = concat('Staging, Test',id); 
  update civicrm_contact set addressee_display = 'Dear Staging';
  update civicrm_contact set email_greeting_display = 'Dear Staging';
  update civicrm_contact set postal_greeting_display = 'Dear Staging';
  
  ```
  
  And as pointed out by @username_3 you will also need to clear the cache tables, but please use the GUI for this as direct SQL on these tables can cripple the CiviCRM caching logic.
  
  Feel free to edit and expand this answer if you know of any other fields that can be cleared by SQL.
  
  
  ---
  
  ### Answer by username_3 on 2020-03-18. Score: 1
  
  While there's a good answer as indicated by the comments above, in this specific issue might be seeing data from the cache in those autocomplete results?
  
  CiviCRM has a UI to clear the cache at something like **Administer > System Settings > Cleanup Caches & Update Paths**
  
  *Using CiviCRM's public interfaces / ux / tools like `civix` are recommended over direct SQL.* But you asked about a specific case (contact table wiped, autocomplete shows retained results), so there you go :)
  
  Your SQL may still not fully anonymize the DB (see other answer), and there's no simple answer which can ensure you do this short of wiping and installing a fresh DB.
  
  > **Comments:**
  > **username_1**: It's probably also sort_name.  
  > **username_0**: Dear username_3, I voted down as although this is sage advice (to use the GUI) it's not an answer. It was the sort_name that Pete and username_1 have mentioned.  
  > **username_3**: No worries, thanks for saying why :)  
  
  ---
  
  