--- In firebird-support@yahoogroups.com, Rich Pinder <rpinder@...> wrote:

>
> I'm trying to accomplish a very simple update of one table, based on
> values from another. (example data below)
>
> These three query approaches seem not to work in FB 1.5.
>
> Is there another SQL way to handle this task in FB??
> > update regtmp a set a.ccssid = b.ccssid from richselect b
> > where a.uscid = b.uscid;
> > UPDATE regtmp
> > SET regtmp.ccssid = richselect.ccssid
> > FROM regtmp, richselect
> > WHERE regtmp.uscid = richselect.uscid
> > UPDATE regtmp
> > SET regtmp.ccssid = richselect.ccssid
> > FROM regtmp INNER JOIN richselect ON regtmp.uscid = richselect.uscid
> EXAMPLE Data
>
> Prior to update:
>
> regtmp
> ccssid ------- uscid
> <null> 001
> <null> 002
> <null> 003
>
> richselect
> ccssid--------uscid
> 4444 001
> 8888 003
>
> After update:
>
> regtmp
> ccssid ------- uscid
> 4444 001
> <null> 002
> 8888 003
>

The following query should do it.

update regtmp rt
set rt.ccssid =
(
select rs.ccssid
from richselect rs
where rs.uscid = rt.uscid
);

But you may want to consider the design of your tables. A normalised
database should not have duplicate data in that way. Why does regtmp
need to hold ccssid when that information is (or seems to be) implied
by uscid?

Adam