Re: UPDATE and outer joins

On Wed, Oct 08, 2003 at 12:23:04 +0100,
  Harry Broomhall <harry.broomhall@uk.easynet.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.