Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    30

    Unanswered: DELETE Top n rows

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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
    )

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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;

  4. #4
    Join Date
    May 2002
    Posts
    30
    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

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •