Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id BFC5F634606 for ; Wed, 29 Jul 2009 10:44:50 -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 25891-03 for ; Wed, 29 Jul 2009 13:44:31 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from QMTA01.westchester.pa.mail.comcast.net (qmta01.westchester.pa.mail.comcast.net [76.96.62.16]) by mail.postgresql.org (Postfix) with ESMTP id D0B546324F2 for ; Wed, 29 Jul 2009 10:44:39 -0300 (ADT) Received: from OMTA01.westchester.pa.mail.comcast.net ([76.96.62.11]) by QMTA01.westchester.pa.mail.comcast.net with comcast id Mo561c0090EZKEL51pjboT; Wed, 29 Jul 2009 13:43:35 +0000 Received: from [10.0.1.2] ([71.225.82.239]) by OMTA01.westchester.pa.mail.comcast.net with comcast id Mpke1c00l59opWn3MpkfwT; Wed, 29 Jul 2009 13:44:39 +0000 Cc: =?ISO-8859-1?Q?=22Gau, _Hans-J=FCrgen=22?= , PgSQL-sql Message-Id: From: Daryl Richter To: nha In-Reply-To: <4A6F695D.7070000@free.fr> Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes Content-Transfer-Encoding: quoted-printable Mime-Version: 1.0 (Apple Message framework v935.3) Subject: Re: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL Date: Wed, 29 Jul 2009 09:44:37 -0400 References: <670F563845D6B34189933C9C7322B4872C0568@LV-MX-00002-V02.LV.ads.niedersachsen.de> <215A7F0B-D033-4C12-B80E-4A4E4613FD41@comcast.net> <4A6F695D.7070000@free.fr> X-Mailer: Apple Mail (2.935.3) 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/144 X-Sequence-Number: 32885 On Jul 28, 2009, at 5:10 PM, nha wrote: > Hello, > > Le 28/07/09 14:25, Daryl Richter a =E9crit : >> >> On Jul 28, 2009, at 5:58 AM, Gau, Hans-J=FCrgen wrote: >> >>> hello list, >>> i have some problems with an sql-statement which runs on oracle but >>> not on postgresql (i want update only if result of SELECT is not >>> empty, the SELECT-queries are identical): >>> >>> UPDATE table1 t1 >>> SET (t1.id) =3D >>> (SELECT h.id FROM table2 t2,table3 t3, table1 t1 >>> WHERE t3.field =3D t2.field >>> AND t2.id =3D t1.id >>> AND t1.id <> t3.id) >>> WHERE >>> (SELECT h.id FROM table2 t2,table3 t3, table1 t1 >>> WHERE t3.field =3D t2.field >>> AND t2.id =3D t1.id >>> AND t1.id <> t3.id) IS NOT NULL; >>> >> Try this: >> >> UPDATE table1 t1 [...] >> WHERE >> EXISTS (SELECT 1 FROM table2 t2,table3 t3, table1 t1 >> WHERE t3.field =3D t2.field >> AND t2.id =3D t1.id >> AND t1.id <> t3.id >> >> AND h.id IS NOT NULL); >> > > Beyond the solution brought by Daryl Richter, it seems that "h" is an > unbound alias in the original (and also in the suggested) query. Some > clarification would be helpful for further investigation. Ahh, you're right. I didn't even notice that, just reformatted the =20 OPs query. > > Regards. > > -- > nha / Lyon / France. > > --=20 > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Daryl