Doug's Home On The Web

It's that time of the year again, to clean out Anthrocon's ride share and room share forums.

I dislike deleting old content, because that just causes lots of bad links from Google (and possibly other sites). Not to mention that my logs fill up with 404s unnecessarily. Instead, this SQL will do the job nicely.

CREATE TABLE temp_nids (nid INT(10) UNSIGNED);

INSERT INTO temp_nids (
   SELECT DISTINCT nid FROM term_node WHERE nid IN (
      SELECT n.nid FROM node AS n
      LEFT JOIN term_node AS tn 
         ON n.nid=tn.nid
      LEFT JOIN term_data AS td 
         ON tn.tid=td.tid
      WHERE 
      (
         td.name LIKE 'Room Share%' 
         OR td.name LIKE 'Ride Share%'
      )
      AND FROM_UNIXTIME(n.created) < '2011-07%'
   )
)

DELETE FROM term_node WHERE nid IN (SELECT nid FROM temp_nids);

The innermost SELECT statement will return the posts made from those forums before last year's convention. It is then wrapped up in a SELECT DISTINCT just to make extra sure that we have valid NIDs. That's probably not strictly necessary, but there aren't any major performance issues with smaller databases, either.

All of those nids are inserted into the temp_nids table, which is then used by the DELETE statement at the end to remove all terms from those nodes, effectively "detaching" those posts from the forums they were originally made in.

End result: the posts aren't removed from the site, just from the forums.