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

[Daryl Richter <ngzax(at)comcast(dot)net>]

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