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: "
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);
For more information
- Dozens more answers to tough DB2 questions from Craig Mullins are available.
- The Best IBM DB2 Web Links: tips, tutorials, scripts, and more.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, Sybase, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on IBM system z and mainframe systems
Related Q&A from Craig S. Mullins
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Craig Mullins explains various definitions of a data mart and sheds some light on the complexity of its relation to a data warehouse. Continue Reading