rifsuda01: Bulk delete entries from Mail.app Previous Recipients using sqlite3

Friday, 27 May 2011

Bulk delete entries from Mail.app Previous Recipients using sqlite3

My company changed its name recently. This meant I was left with hundreds of username@oldname.com entries in Mail.app's Previous Recipients list that I wanted to delete, and a GUI that didn't want to help me do that.

Mail.app does provide a window for viewing and editing the list (Window » Previous Recipients), but its search function only matches against the beginning of names and email addresses. You can't search for substrings, which is what I needed to do in this case.

It turns out, though, that the previous recipients list is just an SQLite v3 database that's stored here:

~/Library/Application Support/AddressBook/MailRecents-v4.abcdmr.

After inspecting the database using the sqlite3 command in Terminal, it transpired that the table you need to modify is called ZABCDMAILRECENT, and the field containing the email addresses is called ZEMAIL. So to delete all the entries whose email address end ...