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