Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 16E236356DE for ; Wed, 29 Jul 2009 07:28:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 90431-02-6 for ; Wed, 29 Jul 2009 10:27:55 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from relais2.altitudetelecom.fr (relais2.altitudetelecom.fr [195.7.112.10]) by mail.postgresql.org (Postfix) with ESMTP id 3E43363582F for ; Wed, 29 Jul 2009 07:23:08 -0300 (ADT) Received: from [192.168.63.22] (unknown [91.151.125.192]) by relais2.altitudetelecom.fr (Postfix) with ESMTP id EAA2165D30A; Wed, 29 Jul 2009 12:23:01 +0200 (CEST) Message-ID: <4A7022C4.9040802@free.fr> Date: Wed, 29 Jul 2009 12:21:56 +0200 From: nha Organization: Lyon, Rhone, France User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728) MIME-Version: 1.0 To: =?ISO-8859-1?Q?=22Gau=2C_Hans-J=FCrgen=22?= CC: PgSQL-sql Subject: Re: WG: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL References: <670F563845D6B34189933C9C7322B4872C089C@LV-MX-00002-V02.LV.ads.niedersachsen.de> In-Reply-To: <670F563845D6B34189933C9C7322B4872C089C@LV-MX-00002-V02.LV.ads.niedersachsen.de> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=-10 required=5 tests=none X-Spam-Level: X-Archive-Number: 200907/141 X-Sequence-Number: 32882 Hello, Le 29/07/09 11:48, Gau, Hans-Jürgen a écrit : > Sorry, it's a lapse by copying and simplification the original version. that > is correct: > > UPDATE table1 t1 > SET (t1.id) = > (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 > WHERE t3.field = t2.field > AND t2.id = t1.id > AND t1.id <> t3.id) > WHERE > (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 > WHERE t3.field = t2.field > AND t2.id = t1.id > AND t1.id <> t3.id) IS NOT NULL; > > or in very simplified form: > > UPDATE table t1 > SET (t1.id)=(SELECT expression) > WHERE (SELECT expression) IS NOT NULL; > > The SELECT expressions are identical. > > this syntax is allowed on postgresql? > > the solution brought by Daryl Richter has no effect. > > Regards, Hans > [...] There is one mistake syntactically speaking and according to PostgreSQL specification for UPDATE statement: column specified in the SET part cannot be qualified with an existing alias for the updated table. See also: http://www.postgresql.org/docs/8.4/interactive/sql-update.html Except this point, the query is valid although I am not sure the result is always what you may expect because there is no relation between any field of the updated table and the SELECT expression in the WHERE clause; table1 is called (in fact, a copy of this table is implied) but no column of this table is bound to one or more of the current updated table1. I may mistake... Regards. -- nha / Lyon / France.