Thread: correlated multi-set update?
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
On Tue, 9 Mar 2004, Marty Scholes wrote: > 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? You're not allowed to alias the update target table, so I think you'd need to remove the f alias and refer to foo anywhere you're currently referring to f.
On Tue, Mar 09, 2004 at 14:48:57 -0700, Marty Scholes <marty@outputservices.com> wrote: > > 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. You can't set more than one column in one phrase, so you need to use something like you tried next. > > Next I tried: > > UPDATE foo f I don't believe you can use an alias on the update table. If you change this and use foo instead of f in the subselects, then the update should do what you want. However it may be more efficient to use a FROM clause and do something like: UPDATE foo SET f1 = b.f1, f2 = b.f2, f3 = b.f3, f4 = b.f4, f5 = b.f5 FROM bar as b WHERE foo.pk = b.pk AND f.pkIN ( SELECT l.pk FROM keylist l );