|
View previous topic :: View next topic
|
| Author |
Message |
Rusikesh
New User
Joined: 04 Feb 2010
Posts: 25
Location: United States
|
|
|
|
Hello All,
How can we Select and Update the multile rows in single SQL query.
I am trying in below way,
I opened a cursor and selected required rows by using Join and on some where condition, now i want to update the selected rows with some status. Also the selected rows i will use for further processing, have written below query, I am using DB2V9.
Can any body look into this and let me know where i missed out.
| Code: |
DECLARE CURONE CURSOR FOR
SELECT A.TAB1F1
,A.EMP_ID
,B.EMP_SAL
,B.EMP_INCR
FROM TABLE1 A
LEFT OUTER JOIN TABLE2 B ON
A.TAB1F1= B.TAB2F1
AND A.EMP_STA = '03'
UPDATE TABLE1
SET A.EMP_STA = '55'
WHERE A.EMP_STA = '03'
AND B.BR_STA_CD = '00'
AND A.EMP_ID = '1111'
ORDER BY A.TAB1F1 DESC
,A.EMP_ID ASC |
Thanks
Rushi. |
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| What do you mean 'missed out'? Did you try the query and it failed, or what? Have you got any error messages that you might like to share with us? Or sample data? |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi Rushikesh,
Please share your error message and why there is ORDER BY clause on UPDATE query?
Regards,
Chandan |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
unless the syntax has changed in vsn 9 and 10
the UPDATE for a cursor is a separate invocation of SQL
and
a JOINed cursor was read only.
Chandan's comment about an ORDER BY clause in a cursor for UPDATE is also relevant.
Rusikesh,
suggest you refer to the manual,
and don't bother us again until you have resolved your pre-compile errors
or run-time errors if you are attempting to execute this thru spufi or qmf.
last but not least, if you are using db2 for servers/pc/unix,
go to another forum. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
You have to go with scrollable cursors which works on ROWSET concept.
Here you can pull all the data once thru ROWSET and then UPDATE them once thru again ROWSET. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Rohit Umarjikar wrote: |
You have to go with scrollable cursors which works on ROWSET concept.
Here you can pull all the data once thru ROWSET and then UPDATE them once thru again ROWSET. |
only if the TS is able to define an UPDATEable cusor,
which he has not.
he has a read only cursor,
so your answer is: WRONG! |
|
| Back to top |
|
|
|
|
