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

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
WHEREt3.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