Hi,
Use below login to do this. Use update and the construct in place of delete.
First way
==========
[myserver::db2inst1::/home/db2inst1] db2 connect to sample
db2 "crea
Database Connection Information
Database server = DB2/AIX64 9.7.6
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
[myserver::db2inst1::/home/db2inst1] db2 "create table test(srno int,name varchar(15),location int,address varchar(10))"
DB20000I The SQL command completed successfully.
[myserver::db2inst1::/home/db2inst1] db2 commit
DB20000I The SQL command completed successfully.
[myserver::db2inst1::/home/db2inst1] db2 "insert into test values(1,'raga',23,NULL)"
DB20000I The SQL command completed successfully.
[myserver::db2inst1::/home/db2inst1] db2 "insert into test values(1,'raga',23,NULL)"
DB20000I The SQL command completed successfully.
[myserver::db2inst1::/home/db2inst1] db2 commit
DB20000I The SQL command completed successfully.
[myserver::db2inst1::/home/db2inst1] db2 "select * from test"
SRNO NAME LOCATION ADDRESS
----------- --------------- ----------- ----------
1 raga 23 -
1 raga 23 -
2 record(s) selected.
[myserver::db2inst1::/home/db2inst1] db2 "select rowid,test.* from test"
1 SRNO NAME LOCATION ADDRESS
----------------------------------- ----------- --------------- ----------- ----------
x'000000000000000400000025840101AC' 1 raga 23 -
x'000000000000000500000025840101AC' 1 raga 23 -
2 record(s) selected.
[myserver::db2inst1::/home/db2inst1] db2 "delete from test where rowid=x'000000000000000400000025840101AC'"
DB20000I The SQL command completed successfully.
[myserver::db2inst1::/home/db2inst1] db2 commit
DB20000I The SQL command completed successfully.
[myserver::db2inst1::/home/db2inst1] db2 "select * from test"
SRNO NAME LOCATION ADDRESS
----------- --------------- ----------- ----------
1 raga 23 -
1 record(s) selected.
Second way
==========
[myserver::db2inst1::/home/db2inst1] db2 "select * from test"
SRNO NAME LOCATION ADDRESS
----------- --------------- ----------- ----------
1 raga 23 -
1 raga 23 -
2 record(s) selected.
[myserver::db2inst1::/home/db2inst1]db2 "select SRNO,NAME,LOCATION,address,row_number() over(partition by SRNO,NAME,LOCATION) as row_num from test"
SRNO NAME LOCATION ADDRESS ROW_NUM
----------- --------------- ----------- ---------- --------------------
1 raga 23 - 1
1 raga 23 - 2
[myserver::db2inst1::/home/db2inst1]db2 "delete from (select SRNO,NAME,LOCATION,address,row_number() over(partition by SRNO,NAME,LOCATION) as row_num from test) where row_num=2"
DB20000I The SQL command completed successfully.
[myserver::db2inst1::/home/db2inst1] db2 "select * from test"
SRNO NAME LOCATION ADDRESS
----------- --------------- ----------- ----------
1 raga 23 -
Cheers, Vijay