Re: Updating fields with sql

[vhamberg@xxxxxxxxx]

I'd sent a response on the 13th. Here it is again  <g>

You might try a correlated subquery. The subquery can
refer to the outside
fields without the join. You could use an alias for the
file, say "o" for
outer and "i" for inner, just to be short.

UPDATE NCTSF/"RNK.PCTG" o SET o.pcvbch = 0 WHERE
o.pcvbch <> 0
and (digits(o.PCYEAR) concat digits(o.PCSKEY)) in
(SELECT (digits(i.PPYEAR )
concat digits(i.PPSKEY)) from NCTSF/"RNK.PPAR" i
WHERE i.PPYEAR = o.PCYEAR AND i.PPSKEY = o.PCSKEY AND
i.PPROLL <> 'V')

In fact, since the field names are distinct, you could
leave the alias out,
I think. But they might make it easier for the optimizer

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

I THINK it is basically running the subselect for each
record that passes
the first test (pcvbch <> 0).

> 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.
>