Print Page | Close Window

SQL Code Help

Printed From: LogSat Software
Category: Spam Filter ISP
Forum Name: Spam Filter ISP Support
Forum Description: General support for Spam Filter ISP
URL: https://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=6087
Printed Date: 31 January 2025 at 4:04am


Topic: SQL Code Help
Posted By: jerbo128
Subject: SQL Code Help
Date Posted: 30 May 2007 at 5:39pm

Can someone please suggest a bit of SQL code that will compare the tblMSGS and tblQuarantine and delete the orphaned entires.  My quarantine table has 400K entries and my messages table has 750K.  I'm not real handy with SQL queries, but can usually tweak pre-mades to fit my situation. 

Thanks for the help

jerbo182


Replies:
Posted By: sgeorge
Date Posted: 30 May 2007 at 6:06pm
Hey man, happy to help.  Since I tend to delete from the tlbQuarantine table instead of from tblMsgs, I too need to clean out the orphans from time to time.  Here's how to delete orphaned records in the message table:

The following SQL code works in MS Access 2000 database.
DELETE *
FROM tblMsgs AS m
WHERE (m.MsgID  in (
select m2.msgid
from tblMsgs m2
left outer join tblQuarantine AS q on m2.msgid = q.msgid
where q.msgid is null
))

To view the records before deleting them, replace "DELETE" with "select" in the query above

Because of the way that this relational database is structured, it should not be possible for orphaned quarantine entries to exist.  Hence, you don't have to worry about using a reciprocal query for deleting orphaned tblQuarantine records.    When you delete a record from the tblMsgs table, any records in tblQuarantine that referenced that message record should be automatically deleted.

I hope that helps,

Stephen

Posted By: LogSat
Date Posted: 30 May 2007 at 6:32pm
Strange. The newer versions of SpamFilter (as of the last few months) should perform this orphan cleanup themselves...

While we look for potential problems, the query we use is (should work on all DB platforms):

DELETE tblMsgs FROM tblMsgs LEFT JOIN tblQuarantine
ON tblMsgs.MsgID = tblQuarantine.MsgID WHERE (tblQuarantine.MsgID IS NULL)




-------------
Roberto Franceschetti

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP

Posted By: sgeorge
Date Posted: 31 May 2007 at 10:24am
Well for my part, I'll clarify that my orphans are due to my specific database usage, and don't represent any problem with the software...

Here's what I do when I search and clean my database...
  • I make a copy of my MS Access 2000 quarantine to an offline copy
  • In the offline copy, I delete several records, often deleting only from the tblQuarantine table while neglecting the record in tblMsgs that is referenced.
  • Due to the partial deletes that I run, I leave some orphans in tblMsgs, so I check for and delete orphans before applying the edited database back to SpamFilter.
Stephen

Posted By: jerbo128
Date Posted: 05 June 2007 at 10:49pm

I have some users that refuse to check their quarantine except for once every week.  They then accumulate so many messages, that my webserver times out when they try to delete 4000 messages at one time.  I have come up with a bit of sql that will search the quarantine for users with more than 2000 messages.  It is:

SELECT emailto, COUNT(1) AS Number2
FROM  tblQuarantine as tblQuarantine_2
GROUP BY emailto
HAVING  (COUNT(1) > 2000)

What I would like to do is this:
Say a user has 2500 emails in quarantine - I would like the query to expire the 500 of them that are the oldest.   I can make the query work to delete all emails for the users returned from above, but have been unable to only delete the "excessive" messages.

Is anyone doing anything like this?  Comments?...


Print Page | Close Window