Updating one table with another
Updating one table with another
(OP)
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 ...
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 ...
RE: Updating one table with another
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Updating one table with another