DELETE Top n rows

soundharya_s · #1 (**permalink**) 07-06-04, 09:26

Version 8.1 FP5 on AIX

Like SELECT * FROM TABLE1 FETCH FIRST 100 ROWS ONLY , is there an option to issue
DELETE FROM TABLE1 FIRST 100 ROWS ONLY

Thanks
Soundh

n_i · #2 (**permalink**) 07-06-04, 10:06

I think this should work in v8

Code:

DELETE FROM TABLE1 WHERE SOME_UNIQUE_ID IN (
  SELECT SOME_UNIQUE_ID FROM TABLE1 FETCH FIRST 100 ROWS ONLY
)

Damian Ibbotson · #3 (**permalink**) 07-06-04, 16:27

A nice solution I've seen posted here is to create a view on the table including a row_number. Then delete from the view using the row number.

Code:

create view table1view as 
select *
,        row_number() over() rn 
from   table1;

delete from table1view where rn <= 100;

soundharya_s · #4 (**permalink**) 07-07-04, 01:17

Thank You guys .

Damain, How will the row_number solution perform when I have a few million qualifying rows, out of which I select to delete the first 1000 rows . will db2 materialize the view firt before selecting the row numbers 1 to 1000 ? If it does, isn't this very expensive ?

Thanks again

Marcus_A · #5 (**permalink**) 07-07-04, 02:25

DB2 may have to materialize the view because of several reasons. The most common is because a sort is needed (due to an ORDER BY or because of certain types of joins). If DB2 can use an index in lieu of a sort, the materialization can often be avoided. Check the Explain on the query to determine if a sort is required.

Internet Services

Web Development

Digital Marketing

Consumer Tech