Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Unanswered: db2 delete problem

    hi,
    I am trying to delete records from a table base on the result of a subquery with keyword FETCH FIRST 1000 ROWS. The query works in db2 8 version but doesn't work in db2 7.2 version(gives syntax error). Can you please suggest any alternative for this query.

    The query is like this:
    delete from NQ_RESPONSE_DETAIL where RESPONSE_ID in
    (select RESPONSE_ID from NQ_QUESTIONNAIRE_MASTER qrm,NQ_RESPONSE_MASTER rm where qrm.QUESTIONNAIRE_ID=rm.QUESTIONNAIRE_ID and
    (qrm.DELETE_FLAG='Y' or rm.DELETE_FLAG='Y') fetch first 1000 rows only)

    thanks in advance

    regards,
    Satheesh

  2. #2
    Join Date
    Jan 2002
    Location
    USA
    Posts
    53
    try this one.

    with temp (RESPONSE_ID) as (
    select RESPONSE_ID from NQ_QUESTIONNAIRE_MASTER qrm,NQ_RESPONSE_MASTER rm where qrm.QUESTIONNAIRE_ID=rm.QUESTIONNAIRE_ID and
    (qrm.DELETE_FLAG='Y' or rm.DELETE_FLAG='Y') fetch first 1000 rows only )
    delete from NQ_RESPONSE_DETAIL where RESPONSE_ID in (select RESPONSE_ID from temp);

    if it works, let me know.

  3. #3
    Join Date
    Mar 2003
    Posts
    2

    thanks

    hi,
    thanks for the reply. But the query you sent is also giving syntax problem. I think 'fetch' won't work if used in subquery. The error i got is:

    SQL0104N An unexpected token "fetch" was found following
    "rm.DELETE_FLAG='Y')". Expected tokens may include: ")". SQLSTATE=42601

    thanks and regards,
    Satheesh

  4. #4
    Join Date
    Aug 2002
    Location
    Montreal
    Posts
    1
    db2 select count(*) from original_table

    create view temp as (select <colnames from orignial table>, row_number() over(order by <pick a column> as rank) from <original_table>)

    select * from temp

    delete from temp where rank <=1000

    select count(*) from original_table

Posting Permissions

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