RE: Updating fields with sql

["Hammill, Kenneth H." <khhammill@xxxxxxxxxxx>]

I changed it from doing a multi table select to doing
a real join between the tables.  Real joins are almost
always faster then a multi table select This may help a
little, However, finding a way to not have to concat the
fields together for your key would help allot.  Also IN's
are notoriously slow so if you could find a way around that
it would help considerably.

UPDATE NCTSFT16/"T16.PCTG" SET PC_MV_BATCH_NO = 0
WHERE pcvbch <> 0
and (digits(PCYEAR) concat digits(PCSKEY))
in
(SELECT (digits(PCYEAR) concat digits(PCSKEY))
from NCTSFT16/"T16.PPAR" join NCTSFT16/"T16.PCTG" B
on PPYEAR = B.PCYEAR AND PPSKEY = B.PCSKEY
where PPROLL <> 'V')

-----Original Message-----
From: Klein, Ron [mailto:ron.klein@acs-inc.com]
Sent: Wednesday, September 18, 2002 9:30 AM
To: ''MIDRANGE-L'
Subject: Updating fields with sql


This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]
Hi All,
I posted this question a couple of weeks ago on a Friday and didn't receive
any responses so I'll try again with the hope someone else will see it.

I need to update a field in file B based on a selection from file A and a
condition in file B.  Following is the SQL statement I have;

UPDATE NCTSFT16/"T16.PCTG" SET PC_MV_BATCH_NO = 0 WHERE pcvbch <> 0
and (digits(PCYEAR) concat digits(PCSKEY)) in (SELECT (digits(PCYEAR
) concat digits(PCSKEY)) from NCTSFT16/"T16.PPAR",
 NCTSFT16/"T16.PCTG"B
WHERE PPYEAR = B.PCYEAR AND PPSKEY = B.PCSKEY AND PPROLL <> 'V')

It seems to update the field okay but it runs for about 30 minutes on files
that have 16,000 records apiece.  There is a one to many relationship
between file A and file B.  We are trying to do this on V4R4.

Any suggestions on how to refine this thing to speed it up?

TIA
Ron
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.