Re: UPDATE and outer joins

On Wed, Oct 08, 2003 at 15:40:13 +0100,
Harry Broomhall <harry(dot)broomhall(at)uk(dot)easynet(dot)net> wrote:
> Bruno Wolff III writes:
> > On Wed, Oct 08, 2003 at 12:23:04 +0100,
> > Harry Broomhall <harry(dot)broomhall(at)uk(dot)easynet(dot)net> wrote:
> > > I wonder if anybody could give me a few pointers on a problem I face.
> > >
> > > I need to do an UPDATE on table A, from an effective left outer join
> > > on A and another table B. (This is trying to perform a number translation,
> > > where the items that need it are rare.)
> > >
> > > The following points *I think* are relevant:
> > >
> > > 1) The FROM clause in UPDATE should *only* show additional tables,
> > > otherwise I'll get an extra join I didn't want! (IMHO this could do
> > > with being emphasised in the docs.)
> >
> > But that might be the best approach. If you do a left join of A with B in
> > the where clause and then an inner join of that result with A you should
> > get what you want. If the optimizer does a good job, it may not even be
> > much of a hit to do that.
>
> Er - I though that was one of the points I made - you can't get a
> left join in a WHERE clause? If I am wrong about that then could you
> indicate how I might do it?

I slipped on that. I did mean that you could do left join in the from item
list and then join that to the table be updated by using an appropiate
where condition.

>
> I presumed that the left join would have to be in the FROM clause, i.e.:
>
> UPDATE A set cli = num FROM A left join B on (details) WHERE (etc)
>
> I tried this approach early on, and now I think about it I realize I
> didn't have a WHERE clause - which would have done a cross join which would
> have taken forever!

Someone else responded with the same suggestion, but a bit more fleshed out.