RE: Another SQL question

Elvis, this produces the following error. 

UPDATE jacfiles.agcy_ext r1 SET r1.bus_name = (SELECT r2.acnm_name1 FROM
rugr80dat.pmspag00 r2 WHERE r1.mco = r2.agnm_mco and r1.agcy = r2.agnm_agcy)
WHERE EXISTS (SELECT r2.acnm_name1FROM rugr80dat.pmspag00 r2 WHERE r1.mco =
r2.agnm_mco and r1.agcy = r2.agnm_agcy)

[SQL0104] Token . was not valid. Valid tokens: , FROM INTO. Cause . . . . .
:   A syntax error was detected at token ..  Token . is not a valid token.
A partial list of valid tokens is , FROM INTO.  This list assumes that the
statement is correct up to the token.  The error may be earlier in the
statement, but the syntax of the statement appears to be valid up to this
point. Recovery  . . . :   Do one or more of the following and try the
request again: -- Verify the SQL statement in the area of the token ..
Correct the statement.  The error could be a missing comma or quotation
mark, it could be a misspelled word, or it could be related to the order of
clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL
statement because it does not end with a valid clause.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Tuesday, April 04, 2006 10:38 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Another SQL question

UPDATE jacfiles.agcy_ext r1
SET r1.bus_name = (SELECT r2.acnm_name1 
                   FROM rugr80dat.pmspag00 r2
                   WHERE r1.mco = r2.agnm_mco and r1.agcy = r2.agnm_agcy)
WHERE EXISTS (SELECT r2.acnm_name1 
              FROM rugr80dat.pmspag00 r2
              WHERE r1.mco = r2.agnm_mco and r1.agcy = r2.agnm_agcy)

First subselect sets the value and second ensures only matching rows are
updated.

Elvis
 
-----Original Message-----
Subject: FW: Another SQL question

I know the following sql syntax is not correct (I'm new at attempting SQL)
but this is what I want to do. I am attempting to pull the field acnm_name1
from rugr80dat/pmspag00 table and put it into the bus_name field in the
jacfiles.agcy_ext table. These 2 files are tied by agcy_mco=mco and
agnm_agcy = agcy. Any help (without laughing) would be appreciated.

UPDATE jacfiles.agcy_ext r1

SET bus_name = acnm_name1

WHERE EXISTS (SELECT 1 FROM rugr80dat.pmspag00 r2)

WHERE r1.mco = r2.agnm_mco and r1.agcy = r2.agnm_agcy

 

John A Candidi

Rutgers Insurance Companies

IT Director - AS/400 Manager

856-779-2274