Hello,
My company recently deployed Pg 7.4.1. on Solaris for an experimental
project and is using the experience to evaluate its viability for
migration from Oracle 7.0.
While I like a lot of the features of Pg, one thing I noticed that
"seems" to be missing is the ability to set multiple fields in an update
using a correlated subquery.
For example, I have a statement that copies fields from a template (bar)
into another table (foo) based on a list of keys in a third table (keylist):
UPDATE foo f
SET (f1, f2, f3, f4, f5) = ( SELECT f1, f2, f3, f4, f5 FROM bar b WHERE f.fk = b.pk )
WHERE f.pk IN ( SELECT l.pk FROM keylist l );
In Oracle this works wonders, but it seems to fail under Pg because Pg
wants single field updates and does not allow subqueries.
Next I tried:
UPDATE foo f
SET f1 = ( SELECT f1 FROM bar b WHERE f.fk = b.pk ),
f2 = ( SELECT f2 FROM bar b WHERE f.fk = b.pk ),
f3 = ( SELECT f3 FROM bar b WHERE f.fk = b.pk ),
f4 = ( SELECT f4 FROM bar b WHERE f.fk = b.pk ),
f5 = ( SELECT f5 FROM bar b WHERE f.fk = b.pk )
WHERE f.pk IN ( SELECT l.pk FROM keylist l );
That seemed to get closer, but still barfed (apparently) because of a
lack of table aliasing and correlated subqueries. This makes the
process become an iterative one.
Am I missing something here?
Thanks in advance.
Sincerely,
Marty