Deleting the top 10 rows in a table

My requirement is to delete the top 10 rows of a particular table on DB2. Any pointers on why I'm receiving an error?

In this example, the table and data are as follows.

       IDX         MARKS     
   ----------- -----------
          1          20
          1           5
          1           1
          1          40
          1          13

IDX is the subject id. A mix of delete and fetch were used as follows to delete the records with the lowest marks:

delete from exam where idx = 1 and
marks in (select marks from exam where
idx = 1 order by marks fetch first 2
rows only)

But this gives the following error:

SQL0104N  An unexpected token "delete
from exam where idx = 1 and marks i"
was found following "BEGIN-OF-
STATEMENT".  Expected tokens may
include: 
"
 
  ".  SQLSTATE=42601

 

In this example, the DB2 version is 7.2.7.

The general format of the SQL DELETE statement is as follows:

DELETE FROM table-name (or view-name) 
WHERE search-condition

So far, so good, except the search-condition is miscoded. The search-condition can contain a subquery such as you have coded, but keep in mind that a subquery is a subselect or a fullselect used within a search condition. But you are using both the fetch-first clause and an order-by clause, neither of which are part of a fullselect - they are part of the SELECT statement. So, you will have to find another method of deleting the rows you are looking to delete.

One approach would be to issue multiple DELETE statements, one for each of the TOP n you wish to remove. For example:

delete from exam 
where idx = 1 
and   marks = (select min(marks) from exam);

Each time you issue this statement you will delete from the top of the list of marks.

Or, you could load the results of the following select into a temporary table:

select   idx, marks
from     exam
order by marks
fetch first n rows only;

Use this query to populate a temporary table, say tmp1. Then you can run the following:

delete from exam 
where idx = 1 
and   marks in (select marks from tmp1);