Bulk delete entries from Mail.app Previous Recipients using sqlite3

May 23, '11 07:30:00AM

Contributed by: calum

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 ended with @oldname.com, I simply typed these commands (bold parts are what I typed, non-bold is terminal prompt/output):

[bash-3.2$] sqlite3 ~/Library/Application Support/AddressBook/MailRecents-v4.abcdmr
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select ZFIRSTNAME,ZLASTNAME,ZEMAIL from ZABCDMAILRECENT where ZEMAIL like '%@oldname.com';
This will show a list of records that will be deleted, for visual confirmation. If it looks right, continue with the next SQLite command:
sqlite> delete from ZABCDMAILRECENT where ZEMAIL like '%@oldname.com'; 
sqlite> .quit
And boom... they're gone. Obviously with some knowledge of SQLite commands you can delete other combinations of entries, or even add new ones (although that would be a bit odd). There is of course potential to delete things you didn't mean to, so make sure you have a backup of the MailRecents-v4.abcdmr file before you start.

[crarko adds: I haven't tested this one.]

Comments (8)


Mac OS X Hints
http://hints.macworld.com/article.php?story=20110516152604993