Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    May 2006
    Posts
    82

    Unanswered: How to delete Third row in a table.

    Hi folks,

    I have been posted with a tough question, HOW TO DELTE THIRD ROW in a table. Ofcourse this is not related to my work or project. Its just a challenging question. I need a SQL query to delete 3 rd row of a table if there is one. I know the data may be stored randomly and deleting the single row with out any keys in the where predicate may sound strange.

    First if we run the select * from table name, we will see the rows.
    If we run the delete query to delete 3rd row and again run the select * FROM table name, we should not see the 3rd row. I have no information other than the tablename. Any suggestions would be helpful. Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Table db2admin.tab:
    Code:
    ID  Name
    1   AAA
    2   BBB
    3   CCC
    4   DDD
    SQL (delete third row from table):
    Code:
    DELETE FROM DB2ADMIN.TAB WHERE ID = 
    (SELECT ID FROM 
        (
        SELECT ROW_NUMBER () OVER () AS NUMBER, ID FROM DB2ADMIN.TAB
        )
     AS TEMP WHERE NUMBER = 3
    )
    First of all you need to know this is dangerous operation, because you don't know which data will be deleted. It can help if order by statement is used in select, but you never know what will be deleted. It should be used primary key in delete where condition where ever possible.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 03-02-07 at 05:25.

  3. #3
    Join Date
    May 2006
    Posts
    82
    Hi Grofaty,

    Thank you very much. I agree with your suggestion. We are no where using this query however this was a challengin question to me.

    I forgot to inform that i need this query for OS/390 or Z/OS. I believe ROW_NUMBER () OVER () are the function on LUW. I did tried this on Z/OS and it didnt work for me. Would there be any other way. Thank you.
    Please help

    Thanks,
    Vinay

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    I don't have db2 for os/390 or z/OS to test. But if I remember correctly this LUW functions should work in db2 v8 for zOS. Not sure...

    Ok, try this out, don't know it is working on zOS:
    Code:
    DELETE FROM DB2ADMIN.TAB WHERE ID = (
    SELECT ID FROM (
    SELECT ID FROM DB2ADMIN.TAB FETCH FIRST 3 ROWS ONLY
    ) AS TEMP
    EXCEPT
    SELECT ID FROM (
    SELECT ID FROM DB2ADMIN.TAB FETCH FIRST 2 ROWS ONLY
    ) AS TEMP2
    )
    Hope this helps,
    Grofaty

  5. #5
    Join Date
    May 2006
    Posts
    82
    Hi, Thanks again.

    It didnt work for me. It says error at FETCH. I believe we cannot use FETCH clause in sub queries.(Not sure though).

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    Are you trying to delete the row with the 3rd lowest value of the ID column?

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I would suggest the following:
    Code:
    DECLARE c CURSOR FOR
       SELECT 1 FROM tbl ORDER BY ... 
       OPTIMIZE FOR 3 ROWS
       FOR UPDATE ;
    OPEN c ;
    FETCH c ;
    FETCH c ;
    FETCH c ;
    DELETE FROM tbl WHERE CURRENT OF c ;
    Last edited by Peter.Vanroose; 03-02-07 at 10:13.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by grofaty
    I don't have db2 for os/390 or z/OS to test. But if I remember correctly this LUW functions should work in db2 v8 for zOS.
    No, these functions were not added to DB2 v8 for z/OS.
    Hopefully they are in DB2 9 (to be out very soon ...)
    Last edited by Peter.Vanroose; 03-02-07 at 18:45.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Peter, Yes, ROW_NUMBER, RANK etc are planned for V9.

    Sathyaram

    Quote Originally Posted by Peter.Vanroose
    No, these functions were not been added to DB2 v8 for z/OS.
    Hopefully they are in DB2 9 (to be out very soon ...)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by vini_srcna
    Hi folks,

    I have been posted with a tough question, HOW TO DELTE THIRD ROW in a table. Ofcourse this is not related to my work or project. Its just a challenging question. I need a SQL query to delete 3 rd row of a table if there is one. I know the data may be stored randomly and deleting the single row with out any keys in the where predicate may sound strange.

    First if we run the select * from table name, we will see the rows.
    If we run the delete query to delete 3rd row and again run the select * FROM table name, we should not see the 3rd row. I have no information other than the tablename. Any suggestions would be helpful. Thanks in advance
    Your question doesn't make any sense. Tables are comprised of a set of columns and contain a set of rows. Sets are - per definition - not ordered. Therefore, no one knows what the "3rd" row will be unless you specify an explicit ORDER BY in your query. Even the same query could return the rows in different order when executed twice - and that would be absolutely correct.

    So you can delete any arbitrary row of your table. Just make sure there are more than 2 rows. You can always find an ORDER BY which happens to place this row in the 3rd position.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    May 2006
    Posts
    82
    I agree stolze. This is a stupid question. However i just tried to find a stupid solution -. I agree we never know which row is deleted and am sure no one uses this kind of logic.

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by vini_srcna
    This is a stupid question. However i just tried to find a stupid solution -
    Hi,
    I think stolze has the best solution to solve the problem: "Don't do it."
    Grofaty

  13. #13
    Join Date
    May 2006
    Posts
    82
    I was expecting your response Grofaty..-..!

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's not a stupid question

    it's a homework assignment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by vini_srcna
    I was expecting your response Grofaty
    There is no such thing as stupid question, there are just stupid people, who are afraid of asking.

    One more advice... Don't migrate to db2 v9. This is the best solution to protect yourself against complex problems. You know: "Simple systems, simple problems. Complex systems complex problems."
    Grofaty

Posting Permissions

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