Gaetano Mendola <mendola@bigfoot.com> writes:
> NikhilS wrote:
>> I have tried some update-subselect variations and they seem to work. For
>> example the case in the src/test/regress/sql/update.sql, which used to
>> fail till now, seems to work:
>>
>> UPDATE update_test SET (a,b) = (select a,b FROM update_test where c =
>> 'foo')
>> WHERE a = 10;
> What's the expected result if the tuple from subselect is more than 1?
Error, per SQL99 section 7.14:
1) If the cardinality of a <row subquery> is greater than 1 (one), then an exception condition is
raised:cardinality violation.
> I expect no update at all in case of void result set, is this the case ?
No, you get nulls; it's a subquery not a join. Per SQL99 7.1:
c) If the <row value constructor> is a <row subquery>, then:
i) Let R be the result of the <row subquery> and let D be the degree of R.
ii) If the cardinality of R is 0 (zero), then the result of the <row value constructor> is D
nullvalues.
iii) If the cardinality of R is 1 (one), then the result of the <row value constructor> is
R.
regards, tom lane
