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: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