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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
