From: Daryl Richter <ngzax@comcast.net>
To: nha <lyondif02@free.fr>
Cc: "Gau, Hans-Jürgen" <Hans-Juergen.Gau@LGN.Niedersachsen.de>
Cc: PgSQL-sql <pgsql-sql@postgresql.org>
Subject: Re: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL
Date: Wed, 29 Jul 2009 09:44:37 -0400
Message-ID: <BB25B038-BD93-44AA-94BC-6195913EECF7@comcast.net> (raw)
In-Reply-To: <4A6F695D.7070000@free.fr>
References: <670F563845D6B34189933C9C7322B4872C0568@LV-MX-00002-V02.LV.ads.niedersachsen.de>
<215A7F0B-D033-4C12-B80E-4A4E4613FD41@comcast.net>
<4A6F695D.7070000@free.fr>
On Jul 28, 2009, at 5:10 PM, nha wrote:
> 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.
Ahh, you're right. I didn't even notice that, just reformatted the
OPs query.
>
> Regards.
>
> --
> nha / Lyon / France.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Daryl
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ngzax@comcast.net, lyondif02@free.fr, Hans-Juergen.Gau@LGN.Niedersachsen.de
Subject: Re: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL
In-Reply-To: <BB25B038-BD93-44AA-94BC-6195913EECF7@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox