Re: Select & Update

[DAsmussen@xxxxxxx]

Prakash,

In a message dated 98-10-29 12:56:16 EST, you write:

<<snip>>
>  Now I want to select all fields of table A and update field A with 
>  Field C and Field B with Field D. When I try to do this via Interacive 
>  SQL , I get an error message saying that an Update to clause cannot be 
>  used with a Select statement. I tried SQLRPG through cursors but the 
>  result was the same. I will be happy if any one may be able to throw 
>  some light on how to do this. I use V4R1. 

Try this:

DECLARE CMAIN CURSOR FOR SELECT * FROM A
OPEN CMAIN

FETCH CMAIN INTO {External Data Structure Based on file A}
(Insert your favorite loop for while SQLCOD = *ZEROS)
UPDATE A SET A=:C,B=:D WHERE CURRENT OF CMAIN
FETCH CMAIN INTO {External Data Structure Based on file A}
ENDDO

CLOSE CMAIN

That's one method.  There's also a "FOR UPDATE OF" clause, but I can't think
of the syntax off the top of my head...

HTH,

Dean Asmussen
Enterprise Systems Consulting, Inc.
Fuquay-Varina, NC  USA
E-Mail:  DAsmussen@aol.com

"Better to be occasionally cheated than perpetually suspicious." -- B.C.
Forbes
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---