delete all rows associated with sql query

Question

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?

I'm not sure. using the query I listed above I appear to get the expected results. I'll try reordering and see if I get the same results. I added a tag for oracle-sqldeveloper. — user3612719
– user3612719, Commented Mar 4, 2015 at 14:10

Rob Conklin · Accepted Answer · 2015-03-04 03:09:29Z

5

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
)

answered Mar 4, 2015 at 3:09

Rob Conklin

9,5291 gold badge23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yuck Over a year ago

This actually isn't lazy in my opinion, and you have the benefit of running the sub-select to verify the rows which will be deleted before actually doing so (if running the command interactively).

Collectives™ on Stack Overflow

delete all rows associated with sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related