UPDATE and outer joins

From: Harry Broomhall <harry(dot)broomhall(at)uk(dot)easynet(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: UPDATE and outer joins
Date: 2003-10-08 11:23:04
Message-ID: 200310081123.MAA22190@haeb.noc.uk.easynet.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Johnson, Shaunn 2003-10-08 13:45:28 Re: using copy to load odd characters
Previous Message Mattias Kregert 2003-10-08 11:04:05 Re: refential integrity to multiple tables ??