Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

Bulk delete entries from Mail.app Previous Recipients using sqlite3 Apps
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.]
    •    
  • Currently 4.50 / 5
  You rated: 5 / 5 (8 votes cast)
 
[6,409 views]  

Bulk delete entries from Mail.app Previous Recipients using sqlite3 | 8 comments | Create New Account
Click here to return to the 'Bulk delete entries from Mail.app Previous Recipients using sqlite3' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Bulk delete entries from Mail.app Previous Recipients using sqlite3
Authored by: knouse on May 23, '11 08:52:21AM

You need a \ to escape the space in Application Support.



[ Reply to This | # ]
Bulk delete entries from Mail.app Previous Recipients using sqlite3
Authored by: da2357 on May 23, '11 09:34:11AM
Good tip, thanks! The GUI in Mail.app doesn't allow you to edit a name, so you can easily run a few more SQL commands to update a record. First, I'll visually confirm the record I want to update with SELECT, issue two UPDATE commands to change first and last name, re-confirm the change and exit:
[bash]$ sqlite3 "~/Library/Application Support/AddressBook/MailRecents-v4.abcdmr"
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select ZFIRSTNAME,ZLASTNAME,ZEMAIL from ZABCDMAILRECENT where ZEMAIL like 'johnsmith@somewhere.com';
||johnsmith@somewhere.com
sqlite> update ZABCDMAILRECENT set ZFIRSTNAME = 'John' where ZEMAIL like 'johnsmith@somewhere.com';
sqlite> update ZABCDMAILRECENT set ZLASTNAME = 'Smith' where ZEMAIL like 'johnsmith@somewhere.com';
sqlite> select ZFIRSTNAME,ZLASTNAME,ZEMAIL from ZABCDMAILRECENT where ZEMAIL like 'johnsmith@somewhere.com';
John|Smith|johnsmith@somewhere.com
sqlite> .quit


[ Reply to This | # ]
Bulk delete entries from Mail.app Previous Recipients using sqlite3
Authored by: auco on May 23, '11 02:57:27PM

I can recommend SQLiteBrowser, an open source GUI for SQLite databases... I suppose it's easier than doing it in the terminal if you're not a SQL pro:
http://sqlitebrowser.sourceforge.net



[ Reply to This | # ]
Bulk delete entries from Mail.app Previous Recipients using sqlite3
Authored by: jaydisc on May 23, '11 05:05:10PM

Can't this be done a lot quicker and easier from the GUI? Window -> Previous Recipients -> [search] -> Remove from List?



[ Reply to This | # ]
Bulk delete entries from Mail.app Previous Recipients using sqlite3
Authored by: chocky on May 24, '11 08:04:22AM

Nope.

As mentioned by the OP Mail's Previous Recipients search function only matches from the beginning of the Name or Email fields. Sub-strings are not matched.



[ Reply to This | # ]
Bulk replace entries from Mail.app Previous Recipients using sqlite3 ?
Authored by: smilec on May 23, '11 10:50:15PM

Great hint, I've tried similar things with the other (Addressbook etc) sqlite databases. Does anyone know how to *replace* domain names? My company changed domain from olddomain.org to newdomain.org and I want to do a sed-like substitution of olddomain with newdomain in the SQL query. Possible?



[ Reply to This | # ]
Bulk replace entries from Mail.app Previous Recipients using sqlite3 ?
Authored by: da2357 on May 24, '11 06:27:01AM
Yes, it's possible to just change the domain name in the email address. I'd suggest first copying the MailRecents-v4.abcdmr file to your Desktop and trying it there before doing it on the live copy.

[bash]$ cp "~/Library/Application Support/AddressBook/MailRecents-v4.abcdmr" ~/Desktop/MailRecents-v4.abcdmr
[bash]$ sqlite3 "~/Desktop/MailRecents-v4.abcdmr"
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select ZFIRSTNAME,ZLASTNAME,ZEMAIL from ZABCDMAILRECENT;
John|Smith|johnsmith@somewhere.com
Mary|Jones|maryjones@yahoo.com
David|Collins|davidcollins@somewhere.com
Frank|Tarbox|franktarbox@gmail.com
sqlite> update ZABCDMAILRECENT
...> set ZEMAIL = replace(ZEMAIL,'somewhere.com','newdomain.com')
...> ;
sqlite> select ZFIRSTNAME,ZLASTNAME,ZEMAIL from ZABCDMAILRECENT;
John|Smith|johnsmith@newdomain.com
Mary|Jones|maryjones@yahoo.com
David|Collins|davidcollins@newdomain.com
Frank|Tarbox|franktarbox@gmail.com
sqlite> .quit

If the results are as you expect/want, then perform it on your live 'MailRecents-v4.abcdmr' file.


[ Reply to This | # ]
Bulk replace entries from Mail.app Previous Recipients using sqlite3 ?
Authored by: westont on May 24, '11 08:27:39AM

Great hint ! Have just successfully updated a domain name in my previous recipients list. As always work on a copy though. :-)



[ Reply to This | # ]