CODEWITHSUNDEEP
sqloracle-sqldeveloper
user3612719
user3612719

Reputation: 129

delete all rows associated with sql query

I have a table with 5 columns (primary_key, abstractid, sentid, wordid, term).

This query pulls up a list of distinct terms that appear in less than 6 distinct abstractid's.

SELECT 
    term, COUNT(distinct abstractid) AS "num" 
FROM 
    nsfabstracts 
HAVING 
   COUNT(distinct abstractid) < 6 
GROUP BY 
   term 
ORDER BY 
   "num" DESC;

How would I modify the above query to count the number of rows it returns instead? Also, how would I delete all rows associated with the above query?

Upvotes: 1

Views: 468

Answers (1)

Rob Conklin
Rob Conklin

Reputation: 9464

Be lazy, use a sub-select:

SELECT count(*) FROM (
     SELECT term, COUNT(distinct abstractid) AS "num" FROM nsfabstracts HAVING COUNT(distinct abstractid) < 6 GROUP BY term
)

and

DELETE FROM nsfabstracts WHERE term in (
     SELECT term, COUNT(distinct abstractid) AS "num" FROM nsfabstracts HAVING COUNT(distinct abstractid) < 6 GROUP BY term
)

Upvotes: 5

Related Questions