Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 6900B6325CC
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	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
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <ngzax@comcast.net>
To: =?ISO-8859-1?Q?=22Gau,
	_Hans-J=FCrgen=22?= <Hans-Juergen.Gau@LGN.Niedersachsen.de>
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