DELETE *
FROM prospects
WHERE ipaddr in (SELECT prospects.ipaddr
FROM prospects
GROUP BY prospects.ipaddr
HAVING (((Count(prospects.ipaddr))>1000))
ORDER BY Count(*) DESC);
My guestbook resides in a MS Access database that I named guestbook. Before creating my guestbook I had a table for prospects in my timeshare resale business; my guestbook is based on the timeshare database, and hence the name of the table in it is called prospects (I could've changed the name, but didn't feel like it). The prospects table has the following structure:
fname Text mname Text lname Text resort Text phone Text email Text comments Memo ipaddr Text fieldnum AutoNumber
You can see in the SQL statement at the top of this blog that I look for records where the IP address of the record's author has occurred over 1000 times; delete all the records found with this criteria. I deleted over 100,000 records by running the SQL statement (a query). This probably wasn't the fairest way to purge records from my guestbook, but it was the quickest way to get rid of records that had been inserted by overly ambitious spammers. I thought this was a good blog for teaching the SQL technique of using nested queries.
Note 02/21/2016 (an explanation for the structure of the update query in this blog): I was just looking at my own blogs, and noticed something about the SQL query in this blog that might be bewildering to my readers. I have an ORDER BY statement that has no effect on the functionality of this update query (and that could be bewildering to my visitors). However, I created the inner query first, too see how many entries particular visitors were putting in my guestbook; that's also why the inner query has a GROUP BY statement. I saw that visitors who had over 1000 entries in my guestbook had over 6000 entries, hence the addition of the HAVING statement; HAVING (((Count(prospects.ipaddr))>1000)), too limit the returned records to those entered by authors with over 1000 entries (and hence, over 6000). I added the outer query, which was a DELETE statement for every record in the prospects table that was also found in the inner query. BTW, the inner query originally showed the fields ipaddr and count(ipaddr) in the SELECT statement; had to remove the count(ipaddr) so the outer query could deal strictly with a list of ipaddr values.