11 Replies Latest reply on Jul 7, 2017 10:18 AM by John Neighbors

    Delete Old E-mail with Specific Subject

    TPorte Apprentice

      We've sent out thousands of e-mail messages over the year with a specific e-mail subject. 

       

      Now, we would like to delete all of those emails out of our history with a SQL query.  How would I go about doing this? Goldmine Premium 2015.  Any help from my SQL expert friends would be appreciated.

        • 1. Re: Delete Old E-mail with Specific Subject
          John Neighbors Expert

          Terry,

           

          Recognize that sent emails filed in History actually reside in two distinct tables, behind-the-scenes at the SQL level, CONTHIST and MAILBOX.

           

          Are you specifically only wanting to delete such from CONTHIST (which would keep them from showing up on the History tab), thus leaving the MAILBOX counterparts?  Or, are you wanting to CLEANLY delete BOTH components (which is what is usually recommended)?

           

          Lastly, know that some folks may respond to your post with warnings about deleting items at the SQL level and GoldMine sync-awareness.  Knowing that you all do NOT sync your GoldMine, you should be fine doing this at the SQL level, just as long as it's done properly.

           

          Let me know your true desire/intent and I'll do my best to be helpful.

          • 2. Re: Delete Old E-mail with Specific Subject
            digitalmarketingstrategist Rookie

            I have the same similar question.  Looking to delete mail before a certain date.  Would like to delete both the history and email from the mailbox tables.  Our database is simply getting too big - definitely want to reduce the size and CONHIST and MAILBOX are the biggest hogs.  Email campaigns seem to be the biggest clutter.  I see a bunch of other things stored in the mailbox table like X-GM-TEMPLATES.  I'd like to delete any mail - filed, sent, in trash, etc...  But wouldn't want to delete anything that we are using today like an email template.  

             

            Not sure what some of these folder hold and if I should delete these also.  Not sure how important it is to keep some of this around:

            X-GM-GROUPS

            X-GM-ICALINFO

            X-GM-PROP-HTMLTAB

            X-GM-HTMLTAB

            X-GM-RULES

            X-GM-SUBSENT - How does this differ from the sent?

            X-GM-SUBFILED - How does this differ from the filed?

            X-GM-WEBIMPORT

            X-GM-SMIME-CA

            X-GM-TD-ITEMS

            X-GM-SURX-GMRHEAAO00 - bunch of folders like these for each user

            X-GFY25HC0 (;. $^B - bunch of folders like these for each user

            • 3. Re: Delete Old E-mail with Specific Subject
              John Neighbors Expert

              OK, even without any additional feedback, Terry, I'm hopeful this will help.  Clearly there are many "other" ways to do this. I am purely presenting one approach.

               

              I'm presuming you truly wish to delete BOTH the CONTHIST AND MAILBOX records.

               

              Note that I take an extremely conservative approach when doing this type of "behind the scenes" record deletion.  Therefore, I always backup data before I delete it (even just simple "online" backups within the same SQL database), let some time pass, and THEN delete the "backed up copy" of the data, once an acceptable amount of time has passed. My examples below show how I do the simple "online" backups as well.

               

              In my example below, the KEY is the overall WHERE clause.  Further, once I get the WHERE clause precisely like I want it with the initial SELECT statement, I THEN MAKE SURE to copy THAT EXACT WHERE CLAUSE to the other statements, so they are ALL acting upon the exact same sets of records, no question.

               

              Since GoldMine's SQL Query feature ONLY supports SELECT statements, you will need to use SQL Server Management Studio or some other SQL-based tool that supports all these SQL scripts.

               

              I will provide several snippets of scripts, offering detailed explanation of each script as I go:

               

              -- Identify the desired records to delete

                select conthist.*, mailbox.*

                from conthist

                inner join mailbox on (conthist.LINKRECID = mailbox.RECID)

                where conthist.rectype like 'MO%'

                and conthist.ref like 'Thank you for your business!%'

                and conthist.ondate between '1/1/2001' and '12/31/2001'

               

              *** Explained:

               

                This simply says to display the contents of both tables.  You could easily use a single * as well.

                      select conthist.*, mailbox.*

               

                This is simply the join of the two tables, based on GoldMine database design.  By using an INNER JOIN, I'm  purposefully ONLY picking up applicable records in BOTH tables. (There are many variations of this that could be used, as well.)

                   from conthist

                     inner join mailbox on (conthist.LINKRECID = mailbox.RECID)

               

                OUTBOUND emails (e.g. ones that have been SENT by your organization) have a RECTYPE value that starts with MO (MI is the RECTYPE prefix for INBOUND emails).

                     where conthist.rectype like 'MO%'

               

                The prefix to the SUBJECT of the email.

                    and conthist.ref like 'Thank you for your business!%'

               

                I only want those that were sent within a specific date range.

                     and conthist.ondate between '1/1/2001' and '12/31/2001'

               

              Some other components/logic that could be added to the WHERE clause:

               

                To limit to only those emails sent by SPECIFIC GoldMine IDs:

                    and conthist.userid in ('FRED','MARY','TOM')

               

                You could also do all kinds of record selection based on fields in the MAILBOX table.  Unless there are compelling reasons to NEED to do so, I generally stick to doing this type of record selection based on the CONTHIST table.  Hopefully for Maggie's benefit:  I seldom worry about selecting records based on the FOLDER or FOLDER2 values in the MAILBOX table.  By selecting the CONTHIST.RECTYPE, I can rest assured of the TYPE of emails I am dealing with (vs. all the FOLDER and FOLDER2 coding confusion in the MAILBOX table) and purposefully avoid templates and other "special" records in MAILBOX.

               

              -- Backup CONTHIST records about to be deleted

                select conthist.* into conthist_emails_deleted_20170627

                from conthist

                inner join mailbox on (conthist.LINKRECID = mailbox.RECID)

                where conthist.rectype like 'MO%'

                and conthist.ref like 'Thank you for your business!%'

                and conthist.ondate between '1/1/2001' and '12/31/2001'

               

              *** Explained:

               

                Simply copies all of the applicable CONTHIST data into a new table named conthist_emails_deleted_20170627.

                    select conthist.* into conthist_emails_deleted_20170627

               

                Notice the EXACT same INNER JOIN AND WHERE CLAUSE as was used on the SELECT statement!!!  No need to explain all that again.

               

              -- Backup MAILBOX records about to be deleted

                select MAILBOX.* into mailbox_emails_deleted_20170627

                from conthist

                inner join mailbox on (conthist.LINKRECID = mailbox.RECID)

                where conthist.rectype like 'MO%'

                and conthist.ref like 'Thank you for your business!%'

                and conthist.ondate between '1/1/2001' and '12/31/2001'

               

              *** Explained:

               

                Simply copies all of the applicable MAILBOX data into a new table named mailbox_emails_deleted_20170627.

                    select MAILBOX.* into mailbox_emails_deleted_20170627

               

                Again, notice the EXACT same INNER JOIN AND WHERE CLAUSE as was used on the SELECT statement!!!

                This is imperative to make sure we're strictly dealing with the same records all throughout this process.

               

              -- Delete CONTHIST records just identified

              delete from CONTHIST where recid in (select recid from conthist_emails_deleted_20170627)

               

              *** Explained:

               

                Strictly deletes the CONTHIST records that were JUST NOW backed up via the prior SQL script.

                Notice that with this conservative approach, you will truly ONLY delete what was successfully backed up.

               

              -- Delete MAILBOX records just identified

              delete from MAILBOX where recid in (select recid from mailbox_emails_deleted_20170627)

               

              *** Explained:

               

                Strictly deletes the MAILBOX records that were JUST NOW backed up via the prior SQL script.

                Again, with this conservative approach, you will truly ONLY delete what was successfully backed up.

               

              -- Once time passes

              drop table conthist_emails_deleted_20170627

              drop table mailbox_emails_deleted_20170627

               

              *** Explained:

               

                Once whatever amount of time passes, the above two drop table statements will get rid of the copies of the backed up data.

               

              Well, I think that covers it.  Again, there are many different ways this could be done.  This is purely one approach.  Just be extremely careful and cautious. A philosophy I try to live by in regards to managing data: Never do anything that cannot be undone.

               

              Hope this feedback helps. Please let me know if I need to clarify anything.

              2 of 2 people found this helpful
              • 4. Re: Delete Old E-mail with Specific Subject
                John Neighbors Expert

                Maggie,

                 

                I hope the detailed reply I provided Terry just now is also helpful to you.  I generally steer clear of all this various records in MAILBOX with funny FOLDER and FOLDER2 names, etc.  Yes, there are exceptions and times when I'm working major data cleanup projects for clients when I roll-up my sleeves and dig into MAILBOX in more detail.  However, for a routine process of purging large volumes of previous email blasts and such, I seldom need to deal with MAILBOX in that manner.

                 

                Hope this feedback helps.

                • 5. Re: Delete Old E-mail with Specific Subject
                  TPorte Apprentice

                  John,


                  That looks very thorough and easy to follow.  Thanks for your help with this!

                  • 6. Re: Delete Old E-mail with Specific Subject
                    Shaul.Bel Apprentice

                    Another way maybe: Is to run a search in the email center by the subject then select the all bunch and delete.

                    • 7. Re: Delete Old E-mail with Specific Subject
                      John Neighbors Expert

                      Shaul, I like how you said "maybe".

                       

                      I've had very mixed results with searching the email center based on subject. Well, maybe not even "mixed", but poor results. Even for rather small databases, it seems to churn & churn & churn, but never return results.

                       

                      At any rate, at least with the raw SQL approach, you're almost guaranteed solid AND TIMELY results.

                       

                      Thanks for your input/suggestion, however.  Always appreciate any suggestions that may reveal other options.

                      • 8. Re: Delete Old E-mail with Specific Subject
                        digitalmarketingstrategist Rookie

                        Thanks for the explanation and I like the conservative approach.  Better safe than sorry!

                        I'm still a little curious about what all those "special" Mailbox Records.  Is there any documentation anywhere that says what they are?  I've figured out a some but not all.

                        • 9. Re: Delete Old E-mail with Specific Subject
                          John Neighbors Expert

                          Maggie,

                           

                          I'm not aware of any solid or comprehensive documentation regarding such.  I, too, have simply "figured it out" in some cases, through the years.  I know some of it has to do with the product changing how it codes things behind the scenes now vs. in the past (while having to maintain backward compatibility, etc.).  Newer/fresher installs of GM may not have some of those older items present; while the converse is also true.

                           

                          Anyway, that's my additional 2 cents worth (if that much).

                          • 10. Re: Delete Old E-mail with Specific Subject
                            TPorte Apprentice

                            John,

                             

                            Your solution worked perfectly to delete these old e-mails from history and the mailbox.  Thanks so much!

                            • 11. Re: Delete Old E-mail with Specific Subject
                              John Neighbors Expert

                              Awesome!  Glad to hear it, Terry.