From Hans-Juergen.Gau@LGN.Niedersachsen.de Tue Jul 28 10:28:44 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 6253863474B for ; Tue, 28 Jul 2009 07:28:44 -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 28089-01-2 for ; Tue, 28 Jul 2009 10:28:27 +0000 (UTC) X-Greylist: delayed 00:30:03.513353 by SQLgrey-1.7.6 Received: from INETMAIL01.niedersachsen.de (inetmail01.niedersachsen.de [195.37.201.11]) by mail.postgresql.org (Postfix) with ESMTP id 272F563234F for ; Tue, 28 Jul 2009 07:28:30 -0300 (ADT) Received: from imc2.izn.niedersachsen.de ([195.37.205.24]) by INETMAIL01.niedersachsen.de with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2658.3) id PLNB1873; Tue, 28 Jul 2009 11:58:24 +0200 Received: by IMC2.izn.niedersachsen.de with Internet Mail Service (5.5.2658.3) id ; Tue, 28 Jul 2009 11:57:45 +0200 Message-ID: <670F563845D6B34189933C9C7322B4872C0568@LV-MX-00002-V02.LV.ads.niedersachsen.de> From: =?iso-8859-1?Q?=22Gau=2C_Hans-J=FCrgen=22?= To: pgsql-sql@postgresql.org Subject: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL Date: Tue, 28 Jul 2009 11:58:18 +0200 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2658.3) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CA0F69.EECDB42D" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200907/133 X-Sequence-Number: 32874 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01CA0F69.EECDB42D Content-Type: text/plain; charset="iso-8859-1" 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) = (SELECT h.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 h.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; thanks, hans ------_=_NextPart_001_01CA0F69.EECDB42D Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable sql-porting-problem oracle to postgresql with UPDATE/IS NOT = NULL

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;



thanks, hans


------_=_NextPart_001_01CA0F69.EECDB42D-- From ngzax@comcast.net Tue Jul 28 12:26:14 2009 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6900B6325CC for ; Tue, 28 Jul 2009 09:26:14 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 63576-04 for ; Tue, 28 Jul 2009 12:26:03 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from QMTA08.westchester.pa.mail.comcast.net (qmta08.westchester.pa.mail.comcast.net [76.96.62.80]) by mail.postgresql.org (Postfix) with ESMTP id CFC4E632336 for ; Tue, 28 Jul 2009 09:26:03 -0300 (ADT) Received: from OMTA14.westchester.pa.mail.comcast.net ([76.96.62.60]) by QMTA08.westchester.pa.mail.comcast.net with comcast id MNlo1c0011HzFnQ58QS3uR; Tue, 28 Jul 2009 12:26:03 +0000 Received: from [10.0.1.2] ([71.225.82.239]) by OMTA14.westchester.pa.mail.comcast.net with comcast id MQRs1c00359opWn3aQRseD; Tue, 28 Jul 2009 12:26:03 +0000 Cc: pgsql-sql@postgresql.org Message-Id: <215A7F0B-D033-4C12-B80E-4A4E4613FD41@comcast.net> From: Daryl Richter To: =?ISO-8859-1?Q?=22Gau, _Hans-J=FCrgen=22?= In-Reply-To: <670F563845D6B34189933C9C7322B4872C0568@LV-MX-00002-V02.LV.ads.niedersachsen.de> 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: Tue, 28 Jul 2009 08:25:51 -0400 References: <670F563845D6B34189933C9C7322B4872C0568@LV-MX-00002-V02.LV.ads.niedersachsen.de> 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/134 X-Sequence-Number: 32875 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 =20= > not on postgresql (i want update only if result of SELECT is not =20 > 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 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 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); > > > thanks, hans > > -- Daryl http://itsallsemantics.com ""Everyone thinks of changing the world, but no one thinks of changing =20= himself." - Leo Tolstoy From lyondif02@free.fr Tue Jul 28 21:13:16 2009 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 4E89463568E for ; Tue, 28 Jul 2009 18:13:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 40141-06-5 for ; Tue, 28 Jul 2009 21:13:05 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.insa-lyon.fr (criges14.insa-lyon.fr [134.214.76.242]) by mail.postgresql.org (Postfix) with ESMTP id 78FF4635916 for ; Tue, 28 Jul 2009 18:12:13 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by smtp.insa-lyon.fr (Postfix) with ESMTP id 38618F11E0; Tue, 28 Jul 2009 23:12:11 +0200 (CEST) X-Virus-Scanned: SMTP at INSA-LYON Received: from smtp.insa-lyon.fr ([127.0.0.1]) by localhost (criges14.insa-lyon.fr [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id v8ZDLreyQQtU; Tue, 28 Jul 2009 23:12:09 +0200 (CEST) Received: from [127.0.0.1] (liris.univ-lyon1.fr [134.214.142.10]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: nhandrianar) by smtp.insa-lyon.fr (Postfix) with ESMTPSA id 88C84F11D9; Tue, 28 Jul 2009 23:12:08 +0200 (CEST) Message-ID: <4A6F695D.7070000@free.fr> Date: Tue, 28 Jul 2009 23:10:53 +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: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL References: <670F563845D6B34189933C9C7322B4872C0568@LV-MX-00002-V02.LV.ads.niedersachsen.de> <215A7F0B-D033-4C12-B80E-4A4E4613FD41@comcast.net> In-Reply-To: <215A7F0B-D033-4C12-B80E-4A4E4613FD41@comcast.net> 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/137 X-Sequence-Number: 32878 Hello, Le 28/07/09 14:25, Daryl Richter a écrit : > > On Jul 28, 2009, at 5:58 AM, Gau, Hans-Jürgen 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) = >> (SELECT h.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 h.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; >> > Try this: > > UPDATE table1 t1 [...] > WHERE > EXISTS (SELECT 1 FROM table2 t2,table3 t3, table1 t1 > WHERE t3.field = t2.field > AND t2.id = 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. Regards. -- nha / Lyon / France. From ngzax@comcast.net Wed Jul 29 13:44:50 2009 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