By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,661 Members | 1,330 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,661 IT Pros & Developers. It's quick & easy.

UPDATE and outer joins

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/a
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

Nov 12 '05 #5

P: n/a
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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.