delete records older than 30 days

I am trying to write a stored procedure, but mainly the sql, to delete all records older than 30 days.  The extra part is that there are two tables, sharing a key, and I only want the records from the 2nd table deleted if the date value in the first table is older than 30 days from current date.  The first table data should remain untouched.

This is a simple query to select data across the 2 tables.  m.f_timestamp is the datetime type value I need to compare.

  SELECT
               m.f_message_id,
               m.f_timestamp
               a.f_attachment
        FROM   dbo.t_message AS m,
               dbo.t_attachements AS a
        WHERE  
               m.f_message_id=a.f_message_id

LVL 2
blinkie23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

raterusCommented:
Try this (I guess you are deleting attachments)

Delete
from t_attachments a
where a.f_message_id in (select f_message_id from dbo.t_message where f_timestamp<='6/1/2005')
wstuphCommented:
if you want it to run all the time, you'd want to use something like:

where datediff(dd, f_timestamp, getdate()) > 30
randeepsCommented:
Try this if you want 30 days fromt he current date


Delete
from t_attachments a
where a.f_message_id in (select f_message_id from dbo.t_message where f_timestamp < Convert(Datetime, Convert(Varchar, DateAdd (d, -30, getDate()), 112))

if current date is 01 Jul 2005, it will delete all records before 01 Jun 2005 i.e. from 31 May 2005







Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Anthony PerkinsCommented:
Or:
Delete t_attachements
From t_attachements a
         inner Join t_message m On a.f_message_id = m.f_message_id
Where m.f_timestamp < DATEADD(day, -30, GETDATE())

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blinkie23Author Commented:
Great answers all.  Acperkins, I came up with that easy-to-read solution on my own as well, and am delighted that your answer matches mine so exactly.
randeepsCommented:
If I am not mistaken @acperkins answer does not take into acct the time factor such that if the getdate() returns 01 jul 2005 18:30 then the records from 01 Jun 2005 18:30 will get deleted thus including some records from 01 June. However my solution takes that into acct.

what do you say @acperkins?
Anthony PerkinsCommented:
>>what do you say @acperkins?<<
It is true.  But then you are making the assumption that "30 days from current date" should not include the time.  I was making the assumption the questioner meant "30 days from current date" including the time or did not care, one way or the other, as it was merely intended as a method for clearing out old records, without considering down to the time level.

In conclusion, if that is the case and you want a fast simple delete use my solution. If on the other hand, you meant to delete all records older than 30 days from midnight tonight than consider randeeps solution (I have not tested it).

Thanks for the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.