From: | Daryl Richter <ngzax(at)comcast(dot)net> |
---|---|
To: | nha <lyondif02(at)free(dot)fr> |
Cc: | "Gau, Hans-Jürgen" <Hans-Juergen(dot)Gau(at)LGN(dot)Niedersachsen(dot)de>, PgSQL-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL |
Date: | 2009-07-29 13:44:37 |
Message-ID: | BB25B038-BD93-44AA-94BC-6195913EECF7@comcast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Daryl
From | Date | Subject | |
---|---|---|---|
Next Message | Gau, Hans-Jürgen | 2009-07-29 16:08:15 | WG: WG: sql-porting-problem oracle to postgresql with UPDAT E/IS NOT NULL |
Previous Message | Jasen Betts | 2009-07-29 12:39:52 | Re: Tweak sql result set... ? |