Re: How to use outer join in update

Ragnar <gnari(at)hive(dot)is> writes:
> On fs, 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