update from and left join

From: Tomasz Myrta <jasiek(at)klaster(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: update from and left join
Date: 2007-04-11 07:06:46
Message-ID: evi1du$2hiv$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello

I have a query:

update A set...
from B left join C on (C.col1=B.col1 and C.col2=A.col2)
where ...

which gives me an error:
ERROR: invalid reference to FROM-clause entry for table "A"
HINT: There is an entry for table "A", but it cannot be referenced from
this part of the query.

I found two solutions:
- table A self join 1:1 in "from" part of query,
- table C subqueries in "where" clause - not too comfortable, because I
need col2 value several times.

Any other idea?

The database is PostgreSQL 8.1.5.

Regards,
Tomasz Myrta

Browse pgsql-sql by date

  From Date Subject
Next Message Andrew Sullivan 2007-04-11 13:34:07 Re: A long-running transaction
Previous Message John Summerfield 2007-04-10 21:54:45 Re: A long-running transaction