UPDATE and outer joins

[Harry Broomhall]

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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bruno Wolff III]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Manfred Koizar]

On Wed, 8 Oct 2003 12:23:04 +0100 (BST), Harry Broomhall
<ha*************@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Harry Broomhall]

Bruno Wolff III writes:

On Wed, Oct 08, 2003 at 12:23:04 +0100,
Harry Broomhall <ha*************@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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bruno Wolff III]

On Wed, Oct 08, 2003 at 15:40:13 +0100,
Harry Broomhall <ha*************@uk.easynet.net> wrote:

Bruno Wolff III writes:

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Harry Broomhall]

Manfred Koizar writes:

On Wed, 8 Oct 2003 12:23:04 +0100 (BST), Harry Broomhall
<ha*************@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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html