How to delete Third row in a table.

vini_srcna · #1 (**permalink**) 03-02-07, 03:17

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

grofaty · #2 (**permalink**) 03-02-07, 04:15

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

vini_srcna · #3 (**permalink**) 03-02-07, 06:43

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

grofaty · #4 (**permalink**) 03-02-07, 07:48

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

vini_srcna · #5 (**permalink**) 03-02-07, 08:18

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).

urquel · #6 (**permalink**) 03-02-07, 08:36

Are you trying to delete the row with the 3rd lowest value of the ID column?

Peter.Vanroose · #7 (**permalink**) 03-02-07, 09:06

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 ;

Peter.Vanroose · #8 (**permalink**) 03-02-07, 09:10

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 ...)

sathyaram_s · #9 (**permalink**) 03-02-07, 09:47

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 ...)

stolze · #10 (**permalink**) 03-02-07, 17:57

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.