How to update the status of a particular row as 1 if the whole record already exists?

[madhaviS]

Hiii,
I have data like a sbelow.

emp_id name marks status
1 raga 23 null
1 raga 23 null

There is no primary key for the table.

Now i want the result like
1 raga 23 1----only for the first record

[vijay2082]

Hi,

Use below login to do this. Use update and the construct in place of delete.

First way
==========
[myserver::db2in st1::/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::db2in st1::/home/db2inst1] db2 "create table test(srno int,name varchar(15),loc ation int,address varchar(10))"
DB20000I The SQL command completed successfully.
[myserver::db2in st1::/home/db2inst1] db2 commit
DB20000I The SQL command completed successfully.

[myserver::db2in st1::/home/db2inst1] db2 "insert into test values(1,'raga' ,23,NULL)"
DB20000I The SQL command completed successfully.
[myserver::db2in st1::/home/db2inst1] db2 "insert into test values(1,'raga' ,23,NULL)"
DB20000I The SQL command completed successfully.
[myserver::db2in st1::/home/db2inst1] db2 commit
DB20000I The SQL command completed successfully.
[myserver::db2in st1::/home/db2inst1] db2 "select * from test"

SRNO NAME LOCATION ADDRESS
----------- --------------- ----------- ----------
1 raga 23 -
1 raga 23 -

2 record(s) selected.

[myserver::db2in st1::/home/db2inst1] db2 "select rowid,test.* from test"

1 SRNO NAME LOCATION ADDRESS
----------------------------------- ----------- --------------- ----------- ----------
x'0000000000000 004000000258401 01AC' 1 raga 23 -
x'0000000000000 005000000258401 01AC' 1 raga 23 -

2 record(s) selected.


[myserver::db2in st1::/home/db2inst1] db2 "delete from test where rowid=x'0000000 000000004000000 25840101AC'"
DB20000I The SQL command completed successfully.

[myserver::db2in st1::/home/db2inst1] db2 commit
DB20000I The SQL command completed successfully.

[myserver::db2in st1::/home/db2inst1] db2 "select * from test"

SRNO NAME LOCATION ADDRESS
----------- --------------- ----------- ----------
1 raga 23 -

1 record(s) selected.



Second way
==========


[myserver::db2in st1::/home/db2inst1] db2 "select * from test"

SRNO NAME LOCATION ADDRESS
----------- --------------- ----------- ----------
1 raga 23 -
1 raga 23 -

2 record(s) selected.


[myserver::db2in st1::/home/db2inst1]db2 "select SRNO,NAME,LOCAT ION,address,row _number() over(partition by SRNO,NAME,LOCAT ION) as row_num from test"

SRNO NAME LOCATION ADDRESS ROW_NUM
----------- --------------- ----------- ---------- --------------------
1 raga 23 - 1
1 raga 23 - 2



[myserver::db2in st1::/home/db2inst1]db2 "delete from (select SRNO,NAME,LOCAT ION,address,row _number() over(partition by SRNO,NAME,LOCAT ION) as row_num from test) where row_num=2"
DB20000I The SQL command completed successfully.

[myserver::db2in st1::/home/db2inst1] db2 "select * from test"

SRNO NAME LOCATION ADDRESS
----------- --------------- ----------- ----------
1 raga 23 -

Cheers, Vijay

[vijay2082]

to be more precise on update, below will work for you.

[myserver::db2in st1::/home/db2inst1]db2 "update (select SRNO,row_number () over(partition by SRNO,NAME,LOCAT ION) as row_num from test) set srno=2 where row_num=2"
DB20000I The SQL command completed successfully.
[myserver::db2in st1::/home/db2inst1] db2 "select * from test"

SRNO NAME LOCATION ADDRESS
----------- --------------- ----------- ----------
1 raga 23 -
2 raga 23 -

2 record(s) selected.


Cheers, Vijay