Updating one table with another 1

[ejaggers]

I need to update the column reg_user.fld01 with the value of reg.yearofgrad
for the correct studentid:

- update reg_user
- set reg_user.fld03 = reg.yearofgrad
- where reg_user.screen_num = 11


Table reg
Column name Type Nulls

studentid integer no
fname char(20) no
mname char(15) yes
lname char(25) no
status char(1) no
yearofgrad char(4) no


Table reg_user
Column name Type Nulls

studentid integer no
screen_num smallint no
fld01 char(15) yes
fld03 char(15) yes
fld04 char(15) yes


update reg_user set
fld04 = '1',
fld03 = '1',
fld01 = (select yearofgrad from reg
where status = 'A'
and ...
)
where reg_user.screen_num = 11
and ...

 

[PHV]

Something like this ?

UPDATE reg_user
   SET fld04='1'
      ,fld03='1'
      ,fld01=(SELECT yearofgrad FROM reg
               WHERE status='A'
                 AND studentid=reg_user.studentid
             )
 WHERE screen_num=11
   AND studentid IN(SELECT studentid FROM reg WHERE status='A')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ejaggers]

Work like a dream......Thanks