Thread: How to use outer join in update

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


This does not work in Postgres.

How to convert this statement to Postgres 8.1 ?

Andrus.

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

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

You can use a view for that join query and then create a rule over it to insert in the referenced tables for the inserts in view.

Thanks,
Shoaib

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.

gnari

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

In article <45792BC1.3040305@magproductions.nl>,
Alban Hertroys <alban@magproductions.nl> writes:

> 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

Or this one:

UPDATE t1
SET f1 = t2.f3
FROM t1 x
LEFT JOIN t2 ON x.f2 = t2.f4
WHERE x.f2 = t1.f2

Ragnar <gnari@hive.is> writes:
> On f�s, 2006-12-08 at 10:09 +0100, Alban Hertroys wrote:
>> Andrus wrote:
>>> 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.

I think using a join for this at all is bad style.  What if there is
more than one t2 match for a specific t1 row?  You'll get indeterminate
results, which is not a very good thing for an UPDATE.  In this
particular example you could do

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

This will update to f3 if there's exactly one match, update to NULL if
there's no match (which is what I assume the OP wants, since he's using
a left join), and raise an error if there's multiple matches.  If
you need to not fail when there's multiple matches, think of a way to
choose which one you want, perhaps the largest f3:

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

Of course, you could work out a way to make the join determinate too.
My point is that if you're in the habit of doing this sort of thing
via join, some day you will get careless and get screwed by an
indeterminate update.  If you're in the habit of doing it via subselects
then the notation protects you against failing to think about the
possibility of multiple matches.  (Possibly this explains why there is
no such construct as UPDATE FROM in the SQL standard...)

The problem with the subselect approach of course is what if you need to
transfer multiple columns from the other table row?  You could do

    update t1 set f1 = (select f3 from t2 where t1.f2=t2.f4),
                  f2 = (select f7 from t2 where t1.f2=t2.f4),
                  f3 = (select f9 from t2 where t1.f2=t2.f4);

This works but is just as inefficient as it looks.  The SQL spec
does have an answer:

    update t1 set (f1,f2,f3) = (select f3,f7,f9 from t2 where t1.f2=t2.f4);

but PG does not support that syntax yet :-(.  I'd like to see it in 8.3
though ...

            regards, tom lane