RE: update with select in sql

[Gord Royle <GRoyle@xxxxxxxx>]

Hi James

I just did one like this this morning.

This is the one that I did:

UPDATE PROD/ILN
SET LEXDT =
(SELECT EXPIRE FROM MYLIB/ILNTEMP
    WHERE ILN.LLOT = ILNTEMP.LOTNO   AND
          ILN.LPROD = ILNTEMP.PRODNO)
WHERE EXISTS ( SELECT * FROM MYLIB/ILNTEMP
                  WHERE ILN.LLOT = ILNTEMP.LOTNO   AND
                        ILN.LPROD = ILNTEMP.PRODNO)

This is what I think you need - Please test outside of production:

UPDATE INSHIP
SET SHTEL# =
(SELECT CMTEL FROM ARCUST
    WHERE SHCOMP = CMCOMP   AND
          SHBRAN = CMBRAN   AND
          SHACC  = CMACC    AND
          SHCODE = '00)
WHERE EXISTS ( SELECT * FROM CMTEL
                  WHERE SHCOMP = CMCOMP   AND
                        SHBRAN = CMBRAN   AND
                        SHACC  = CMACC)

HTH

Gord Royle


-----Original Message-----
From: James Rich [mailto:james@eaerich.com]
Sent: Friday, August 30, 2002 12:36 PM
To: midrange-l
Subject: update with select in sql


I need to update a file with values contained in another file (duh).  I
have an SQL select statement that selects the appropriate records I want
to update:

select SHTEL#,CMTEL from inship,arcust where SHCOMP=CMCOMP and
SHBRAN=CMBRAN and SHACC=CMACC and SHCODE='00' and SHTEL# = ' '

What I want to do now is update the SHTEL# field in file inship with the
value in CMTEL in file arcust according to the same conditions specified
above.  Anyone know how to do this in SQL?

James Rich

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