Re: RE: update with select in sql

["Mike Naughton" <mnaughton@xxxxxxxxxxxx>]

I've gotten this sort of thing to work in the past, so I think you're on
the right track, anyway. I usually use this template & modify as necessary:

   UPDATE Table1 SET
     fldOneC = (SELECT fldTwoE FROM Table2
                WHERE KeyTwoA = KeyOneA
                  AND KeyTwoB = KeyOneB),
     fldOneD = (SELECT fldTwoF FROM Table2
                WHERE KeyTwoA = KeyOneA
                  AND KeyTwoB = KeyOneB)
   WHERE EXISTS (SELECT * FROM Table2
                 WHERE KeyTwoA = KeyOneA
                   AND KeyTwoB = KeyOneB)

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?)

#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
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)

Good luck!

midrange-l@midrange.com writes:
>Here is what I tried:
>
>update JAMES/INSHIP set SHTEL# =
>      (select CMTEL from JAMES/ARCUST where
>      SHCOMP = CMCOMP and
>      SHBRAN = CMBRAN and
>      SHACC  = CMACC and
>      SHCODE = '00' and
>      SHTEL# = ' ')
>      where exists (select * from JAMES/ARCUST where
>      SHCOMP = CMCOMP and
>      SHBRAN = CMBRAN and
>      SHACC  = CMACC and
>      SHCODE = '00' and
>      SHTEL# = ' ')
>
>And received this error message:
>
>Token CMTEL was not valid. Valid tokens: + ) -.
>
>I am missing something here?  This seems like it should be an easy thing
>to do...
>
>James Rich


Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-863-4357 x444
mnaughton@juddwire.com