On Wed, Oct 08, 2003 at 15:40:13 +0100, Harry Broomhall <[EMAIL PROTECTED]> wrote: > Bruno Wolff III writes: > > On Wed, Oct 08, 2003 at 12:23:04 +0100, > > Harry Broomhall <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
