CODEWITHSUNDEEP
sqloracle-databasedelete-row
Q-ximi
Q-ximi

Reputation: 961

Cannot delete rows from a table SQL

I am writing a query to delete some rows in my table based on the count (TOTALCOUNT). I would like to delete all rows that count <=5. Below is my query:

DELETE  FROM NSFABSTRACTS WHERE TOTALCOUNT<=5
  SELECT WORD, COUNT(WORD) AS TOTALCOUNT FROM NSFABSTRACTS
  GROUP BY WORD;

I also tried the one below, didn't work either:

DELETE  FROM NSFABSTRACTS 
  SELECT WORD, COUNT(WORD) AS TOTALCOUNT FROM NSFABSTRACTS
  GROUP BY WORD
WHERE TOTALCOUNT<=5;

It is giving me this error:

Error report: SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action:.

Appreciate your help.

Upvotes: 1

Views: 2525

Answers (4)

Sharad
Sharad

Reputation: 813

Try this code

delete from NSFabstracts where fileID in 
(select fileid from nsfabstracts group by word,fileid
having COUNT(word)<=5)

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

DELETE  FROM NSFABSTRACTS 
WHERE WORD IN (
                SELECT WORD FROM NSFABSTRACTS
                GROUP BY WORD
                HAVING COUNT(*)<=5
              )

This removes all those rose containing words which occur less than equal to 5 times in table.

Upvotes: 1

Antonio
Antonio

Reputation: 535

Try:

DELETE  FROM NSFABSTRACTS 
WHERE FILEID IN
(
     SELECT FILEID FROM NSFABSTRACTS
     GROUP BY FILEID, WORD
     HAVING COUNT(Word)<=5
)

Upvotes: 0

Paddy
Paddy

Reputation: 33867

Try:

DELETE  FROM NSFABSTRACTS 
WHERE FileID IN
(
  SELECT FILEID
  FROM NSFABSTRACTS 
  GROUP BY WORD, FILEID
  HAVING COUNT(WORD) <=5;
)

Assuming file ID is your primary key.

Upvotes: 0

Related Questions