From: | Daryl Richter <ngzax(at)comcast(dot)net> |
---|---|
To: | "Gau, Hans-Jürgen" <Hans-Juergen(dot)Gau(at)LGN(dot)Niedersachsen(dot)de> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL |
Date: | 2009-07-28 12:25:51 |
Message-ID: | 215A7F0B-D033-4C12-B80E-4A4E4613FD41@comcast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Akos Gabriel | 2009-07-28 18:12:40 | Detect referential integrity structure |
Previous Message | Gau, Hans-Jürgen | 2009-07-28 09:58:18 | sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL |