correlated multi-set update?
От | Marty Scholes |
---|---|
Тема | correlated multi-set update? |
Дата | |
Msg-id | 404E3BC9.7050000@outputservices.com обсуждение исходный текст |
Ответы |
Re: correlated multi-set update?
Re: correlated multi-set update? |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: