Re: How to use outer join in update

On fös, 2006-12-08 at 10:17 +0000, Ragnar wrote:
> On fös, 2006-12-08 at 10:09 +0100, Alban Hertroys wrote:
> > Andrus wrote:
> > > In my current DBMS I can use
> > >
> > > create table t1 ( f1 int, f2 int );
> > > create table t2 ( f3 int, f4 int );
> > > update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4
> >
> > That looks like a self-join on t1 without using an alias for the second
> > instance of t1.
> >
> > I think you meant:
> > update t1 set f1=t2.f3 from t2 where f2 = t2.f4
>
> is this not effectively an INNER JOIN ?
> the OP needed a LEFT JOIN.

this can be done in 2 operations easily:

update t1 set f1=t2.f3 from t2 where f2 = t2.f4;
update t1 set f1=null
where not exists (select f3 from t2 where f2=f4);

it can also be done in one operation with a
self join:
update t1 set f1=j.f3
from (t1 t1b left join t2 on t1b.f2=t2.f4) as j
where t1.f2=j.f2;

gnari