Want to delete the second occurance of a Key

ysuryawanshi_mcs · Posted: Fri Mar 31, 2006 7:45 pm

I HAV ONE TABLE
EMPNAME(NAME VARCHAR(20))
TABLES VALUES ARE
EMPNAME
YOGESH
YOGESH
SATISH
RAKESH
RAHUL

NOW I WANT TO DELETE A ROW WHERE NAME=YOGESH
BUT I WANT TO DELETE ONLY SECOND YOGESH AND NOT FIRST.

IN ORACLE WE CAN DO
DELETE FROM EMPNAME WHERE NAME='YOGESH' AND ROWID='00000011'
IN ORACLE EACH ROW AND COLUMN IN THE TABLE HAS A UNIQUE IDENTIFIER KNOWN AS ROWID AND COLUMNID.
SO
WHAT IS THIS IN DB2.

martin9 · Posted: Fri Mar 31, 2006 7:56 pm

hy ysuryawanshi_mcs,

if this is your table definition
and you have not the columns like in oracle,
how db2 can determine the right row for deletion???
it is not possible...
try to make any unique key or read the table with cursor for update
and delete the second row where duplicates exist.

martin9

Jag · New User Joined: 07 Dec 2005 Posts: 30 Location: India

Hi,
First thing is in the table which you gave an example there is one column and it doesnt make any sense whether we delete teh fisrt row or second row in the rdbms..Right??.. Second thing if you have any second column in the table then using that coulmn you can delete the row specifying the second column in the where clause.

In DB2 the Rowid(Are hexa decimal) are purely for OS usage to get the rows and the details of the Row ID's are maintained by the system Only..

Cheers,
Jag,
Mail: ***EMail ID REMOVED... Read Forum Rules***