Re: UPDATE using sub selects

Hi,

>
> > 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 null values.
>
> iii) If the cardinality of R is 1 (one), then the result of
> the
> <row value constructor> is R.
>
> regards, tom lane
>

To allow both of the above to hold, I think the subselect will have to be
treated like a EXPR_SUBLINK subquery. I was wondering if we have a similar
mechanism for plain selects/subselects to check and restrict their output to
a single row.

Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com