Delete with join? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Delete with join?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-06-11 : 18:20:35

How can I do the following..(it is for deleting old posts on a snitz database design)

I have 2 tables, FORUM_TOPICS, FORUM_REPLY

I want to delete all Topics, and the corresponding replies where the last post is older than 7 days old

From FORUM_TOPICS I can get T_LAST_POST which is a datetime if this is older than 7 days I want to delete it, the catch is the TOPIC_ID from this same row I need to delete all the rows in FORUM_REPLY where it matches the field TOPIC_ID.

I tried doing this with cascading deletes but I can't seem to get it going because of the way some composite keys are setup.

Any insight Greatly appreciated.

Thanks again,
Mike

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-11 : 18:36:13

CREATE PROCEDURE ClearSnitz @daysOld int AS

DELETE FROM R
FROM FORUM_TOPICS T INNER JOIN FORUM_REPLY R
ON T.TOPIC_ID=R.TOPIC_ID
WHERE DateDiff(dd, T.T_LAST_POST, GetDate()) > @daysOld

DELETE FROM FORUM_TOPICS
WHERE DateDiff(dd, T_LAST_POST, GetDate()) > @daysOld

Call it with:

EXEC ClearSnitz 7

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources