Deleting Anonymous discussion posts from a course

When importing an archive into Blackboard, discussion postings may come over depending on your selection when importing and, of course, the content of the archive you are importing. These posts come in as being posted by Anonymous. This sql can be used to clear out all Anonymous posts in course and group discussion boards for any given course. Note: you must change the 'Your Course_ID goes here' for this to work. (e.g., 'ENGL-101-07IN-S12012').

USE BBLEARN

DECLARE @CourseID varchar(100)

SET @CourseID = 'Your Course_ID goes here'

DECLARE @TempMMpk1 TABLE
(
  pk1 int
)

INSERT into @TempMMpk1(pk1)
SELECT mm.pk1
from msg_main mm
INNER JOIN forum_main fm ON mm.forummain_pk1 = fm.pk1
INNER JOIN conference_main confm ON fm.confmain_pk1 = confm.pk1
INNER JOIN conference_owner co ON confm.conference_owner_pk1 = co.pk1
INNER JOIN course_main cm ON co.owner_pk1 = cm.pk1
WHERE mm.post_as_annon_ind = 'Y' AND cm.course_id = @CourseID AND co.owner_table = 'course_main'

INSERT INTO @TempMMpk1(pk1)
SELECT mm.pk1
FROM msg_main mm
INNER JOIN forum_main fm ON mm.forummain_pk1 = fm.pk1
INNER JOIN conference_main confm ON fm.confmain_pk1 = confm.pk1
INNER JOIN conference_owner co ON confm.conference_owner_pk1 = co.pk1
INNER JOIN groups g ON co.owner_pk1 = g.pk1
INNER JOIN course_main cm ON g.crsmain_pk1 = cm.pk1
WHERE mm.post_as_annon_ind = 'Y' AND cm.course_id = @CourseID AND co.owner_table = 'groups'

DELETE FROM user_msg_state
WHERE msgmain_pk1 IN (SELECT pk1 FROM @TempMMpk1)

DELETE FROM msg_tag
WHERE msgmain_pk1 IN (SELECT pk1 FROM @TempMMpk1)

DELETE FROM msg_attachment
WHERE msgmain_pk1 IN (SELECT pk1 FROM @TempMMpk1)

DELETE FROM msg_label
WHERE msgmain_pk1 IN (SELECT pk1 FROM @TempMMpk1)

DELETE FROM msg_main
WHERE pk1 IN (SELECT pk1 FROM @TempMMpk1)