RE: Updating a record using embedded SQL

Elvis,

I thought I was only getting recs from my NHISXX file based upon the WHERE
clause. So, I am only getting recs that will match the variable office #.
The JOINed file is only necessary to get another piece of data for later
processing in my pgm. Since I am processing by cursor, I wanted to update
only the record I had at the time. Your update would work for the whole
group, though, and achieve the same final result..

Michael Kirkpatrick
Western Dental Services, Inc.
Information Systems / System i Development
(
Phone:
(714) 571.3582
(
Toll free:
(800) 471.4444
7
Fax:
(714) 480.3001
*
Email:
mkirkpatrick@xxxxxxxxxxxxxxxxx
8
Web:
http://www.westerndental.com/





"Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
08/29/2007 10:04 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: Updating a record using embedded SQL






I'm a bit confused. Doesn't your select with LEFT JOIN qualifier return
all
of the "office"s from NHISXX? Being that's the file you're subsequently
updating, wouldn't that mean you're updating each and every row's "fill01"
value to 'X'?
I'm probably missing something, but why couldn't a single statement such
as
this work:

update NHISXX h set fill01 = 'X'
WHERE h.rectyp = 'PA' and h.userid <> ' ' and h.office = :pOffice and
EXISTS

(SELECT 1
FROM NCUSTT c
WHERE c.office = h.office)

Elvis

Celebrating 10-Years of SQL Performance Excellence
http://centerfieldtechnology.com/training.asp

-----Original Message-----
Subject: Updating a record using embedded SQL

This is my first attempt at updating a record using SQL. I believe my
problem lies in the DECLARE stmt because of the JOIN:

declare c1 cursor for
SELECT h.office, chart, crtdat, undata, userid, offno
FROM NHISXX as h
LEFT JOIN NCUSTT as c ON h.office = c.office
WHERE h.rectyp = 'PA' and h.userid <> ' ' and h.office =
:pOffice;

I want to update a field in the NHISXX file but am not able to add the FOR

UPDATE OF clause to the above. I am using a cursor because of other
processing in the pgm.

My update statement is as follows:

update NHISXX set fill01 = 'X'
where current of c1;

Any suggestions on how to best handle this situation? Thanks.