From: | Harry Broomhall <harry(dot)broomhall(at)uk(dot)easynet(dot)net> |
---|---|
To: | bruno(at)wolff(dot)to (Bruno Wolff III) |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: UPDATE and outer joins |
Date: | 2003-10-08 14:40:13 |
Message-ID: | 200310081440.PAA22997@haeb.noc.uk.easynet.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Bruno Wolff III writes:
> On Wed, Oct 08, 2003 at 12:23:04 +0100,
> Harry Broomhall <harry(dot)broomhall(at)uk(dot)easynet(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | John Wells | 2003-10-08 15:23:03 | Humor me: Postgresql vs. MySql (esp. licensing) |
Previous Message | Manfred Koizar | 2003-10-08 14:29:48 | Re: UPDATE and outer joins |