| 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: | Whole Thread | Raw Message | 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 ?? |