Re: UPDATE and outer joins

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.)

2) If a FROM clause is present, but no WHERE clause, a cross join is performed.

3) It is possible to 'emulate' an inner join with statements in a WHERE
clause, but is *not* possible to do so for an outer join. (4.2.2 in the
User's Guide)

If I have the above correct then it seems that there is no way to do
an UPDATE in the way I want in a single statement? I am currently
doing a left outer join into a temporary file, then the UPDATE, but this
involves two joins!

Is there a better way of doing this, or do I have to keep using the
temporary file?

Regards,
Harry.

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.

On Wed, 8 Oct 2003 12:23:04 +0100 (BST), Harry Broomhall
<harry(dot)broomhall(at)uk(dot)easynet(dot)net> wrote:
>I am currently
>doing a left outer join into a temporary file, then the UPDATE, but this
>involves two joins!

UPDATE a
SET col1 = b.col11, col2 = b.col12
FROM a AS x LEFT JOIN b ON (x.??? = b.???)
WHERE a.pk = x.pk;

might not run faster, but at least it is only one statement and you
don't have to care for temporary tables ...

Servus
Manfred

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 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!

Regards,
Harry.

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.

Manfred Koizar writes:
> On Wed, 8 Oct 2003 12:23:04 +0100 (BST), Harry Broomhall
> <harry(dot)broomhall(at)uk(dot)easynet(dot)net> wrote:
> >I am currently
> >doing a left outer join into a temporary file, then the UPDATE, but this
> >involves two joins!
>
> UPDATE a
> SET col1 = b.col11, col2 = b.col12
> FROM a AS x LEFT JOIN b ON (x.??? = b.???)
> WHERE a.pk = x.pk;
>
> might not run faster, but at least it is only one statement and you
> don't have to care for temporary tables ...

Many thanks to all who made suggestions. The above gave me enough
clues to get a working UPDATE query working.

It is a shame it can't be done in a single join though!

Regards,
Harry.