Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id DD0159FB7B6 for ; Wed, 11 Apr 2007 04:06:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 60178-01-8 for ; Wed, 11 Apr 2007 04:06:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id B82099FB7A2 for ; Wed, 11 Apr 2007 04:06:43 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 61D2D3090B; Wed, 11 Apr 2007 09:06:42 +0200 (MET DST) From: Tomasz Myrta X-Newsgroups: pgsql.sql Subject: update from and left join Date: Wed, 11 Apr 2007 09:06:46 +0200 Organization: Hub.Org Networking Services Lines: 24 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/113 X-Sequence-Number: 28113 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