Re: How to use outer join in update

[Ragnar <gnari@xxxxxxx>]

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