From: Daryl Richter <ngzax@comcast.net>
To: "Gau, Hans-Jürgen" <Hans-Juergen.Gau@LGN.Niedersachsen.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL
Date: Tue, 28 Jul 2009 08:25:51 -0400
Message-ID: <215A7F0B-D033-4C12-B80E-4A4E4613FD41@comcast.net> (raw)
In-Reply-To: <670F563845D6B34189933C9C7322B4872C0568@LV-MX-00002-V02.LV.ads.niedersachsen.de>
References: <670F563845D6B34189933C9C7322B4872C0568@LV-MX-00002-V02.LV.ads.niedersachsen.de>
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
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
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);
>
>
> thanks, hans
>
>
--
Daryl
http://itsallsemantics.com
""Everyone thinks of changing the world, but no one thinks of changing
himself."
- Leo Tolstoy
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, Hans-Juergen.Gau@LGN.Niedersachsen.de
Subject: Re: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL
In-Reply-To: <215A7F0B-D033-4C12-B80E-4A4E4613FD41@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