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