> The following query should do it.
>
> update regtmp rt
> set rt.ccssid =
> (
> select rs.ccssid
> from richselect rs
> where rs.uscid = rt.uscid
> );

Right, but that query also updates all rows that are not in richselect
at all, setting their ccssid to NULL. Often, the desired select is
something like:

update regtmp rt
set rt.ccssid =
(
select rs.ccssid
from richselect rs
where rs.uscid = rt.uscid
)
where exists(select *
from richselect rs2
where rs2.uscid = rt.uscid)

(the difference between these two queries would be visible if regtmp had
a value for ccssid for uscid 002 before the update)

Set