deleting specified number of rows?

[daveniemann]

Hi!

any idea on how i would issue a delete statement that would only delete up to 500 rows (the equivalent of rownum in oracle)

oracle equivalent-->delete from sometable where rownum < 501

thanks

 

[sathyarams]

The SALES table is a table in the SAMPLE database. The following sequence of sqls delete the top 9 salaried rows

HTH

sathyaram


$ db2 "create table delsales like sales"
DB20000I The SQL command completed successfully.
$ db2 "insert into delsales select * from sales"
DB20000I The SQL command completed successfully.
$ db2 "select count(*) from delsales"

1
-----------
41

1 record(s) selected.

$ db2 describe table delsales

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- -----
-
SALES_DATE SYSIBM DATE 4 0 Yes

SALES_PERSON SYSIBM VARCHAR 15 0 Yes

REGION SYSIBM VARCHAR 15 0 Yes

SALES SYSIBM INTEGER 4 0 Yes


4 record(s) selected.

$ db2 "create view delsales_view as select sales_date,rownumber() over (order by sales desc) as row# from delsales"
DB20000I The SQL command completed successfully.
$
$ db2 "delete from delsales_view where row#< 10"
DB20000I The SQL command completed successfully.
$ db2 "select count(*) from delsales"

1
-----------
32

1 record(s) selected.




For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[stevexff]

An alternative is to open a cursor on the table, and iterate fetching each row, using DELETE ... WHERE CURRENT OF cursorname on the first n rows. But it's a bit crude, especially for large values of n.

But relational tables aren't flat files, and in sathyarams' example the 'first 500' rows selected for deletion are dependent on the ORDER BY clause.

Depending on what you are trying to achieve, you might be better off deleting all rows that meet some other criterion, like older than CURRENT DATE - 3 MONTHS for example.