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