Re: RE: update with select in sql

[James Rich <james@xxxxxxxxxxx>]

On Fri, 30 Aug 2002, Mike Naughton wrote:

> I don't see any obvious (to me) errors, but I wonder two things:
>
> #1: does your select CMTEL product a unique result for each SHCOMP,
> SHBRAN, SHACC combo? If not, that would explain your error (SQL is
> expecting a single value -- otherwise, how will it know what to set SHTEL#
> to?)

Yes.  *COMP, *BRAN, and *ACC have a one-to-one correspondence.

> #2: are the field names in INSHIP prefixed by SH and the field names in
> ARCUST prefixed by CM? If so (big if), selecting from ARCUST based on

Yes.

> SHCODE='00' and SHTEL#=' ' doesn't really make any sense (?). Instead, you
> might want to try:
>
> update JAMES/INSHIP set SHTEL# =
>       (select CMTEL from JAMES/ARCUST where
>       SHCOMP = CMCOMP and
>       SHBRAN = CMBRAN and
>       SHACC  = CMACC)
>       where
>       SHCODE = '00' and
>       SHTEL# = ' ' and
>       exists (select * from JAMES/ARCUST where
>       SHCOMP = CMCOMP and
>       SHBRAN = CMBRAN and
>       SHACC  = CMACC)

Tried the above and it fails with the same error.  It always says that
CMTEL is an invalid token.  But everything I read indicates that the above
is the correct syntax.  I'm starting to wonder if perhaps OS/400 is
lacking some SQL methods?

James Rich