Updating one table with another 1

ejaggers · Apr 1, 2009

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 · Apr 1, 2009

Something like this ?

Code:

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 · Apr 1, 2009

Work like a dream......Thanks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Updating one table with another 1

ejaggers

Programmer

PHV

MIS

ejaggers

Programmer

Similar threads

Part and Inventory Search

Sponsor