Regarding: row-wise update.
In a previous thread:
http://archives.postgresql.org/pgsql-sql/2008-08/msg00122.php
it was mentioned that the "UPDATE ... SET ... FROM ... WHERE" syntax
is an extension. (an extension employed by many other db flavors.)
This creates a problems since each db flavor is left to their own
devices in how to implement it.
However the ANSI SQL syntax can be a real hassle when you have to a
lot of fields to update using (complicated) correlated sub-queries:
UPDATE Foo
SET a = ( SELECT newA FROM Bar WHERE foo.id = Bar.id ),
b = ( SELECT newB FROM Bar WHERE foo.id = Bar.id ),
c = ( SELECT newC FROM Bar WHERE foo.id = Bar.id ),
...
n = ( SELECT newN FROM Bar WHERE foo.id = Bar.id )
WHERE
Foo.id < 100;
Maybe a row-wise update could simplify this process and at the same
time produce results in with those defined by the SQL Standard.
UPDATE Foo
SET ( a, b, c, ..., n ) = ( SELECT newA, newB, newC, ..., newN
FROM Bar WHERE foo.id = Bar.id )
WHERE
Foo.id < 100;
Any thoughts on this?
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
