Обсуждение: 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.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.
On Wed, 8 Oct 2003 12:23:04 +0100 (BST), Harry Broomhall
<harry.broomhall@uk.easynet.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.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.
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.broomhall@uk.easynet.net> wrote: > Bruno Wolff III writes: > > 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. > > 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.broomhall@uk.easynet.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.