Deleting the top 10 rows in a table
Find out the best way to use the SQL DELETE statement to delete rows from 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);