Re: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL

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

In response to

Responses

Browse pgsql-sql by date

  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