Re: update ... set ... subquery

guys,
sorry my query is like so:
update a
set number = any ( select number from b where a.name=b.name )
where number is null;

simplified table a:
sn | number | name
----------------------------------
1 | 101 | john
2 | null | mary
3 | 127 | mark
4 | null | jane
5 | 133 | matt

simplified table b:
sn | number | name
----------------------------------
1 | 101 | john
2 | 967 | mary
3 | 127 | mark
4 | 965 | jane
5 | 133 | matt

so basically trying to get "number" from "b" to "a" where their "name"s match.

also "... number in (...) ..." throws error (syntax error at or near "in").
thks, jzs

On Wed, Mar 17, 2010 at 3:52 PM, Szymon Guz <mabewlun(at)gmail(dot)com> wrote:
>
> 2010/3/17 John Smith <jayzee(dot)smith(at)gmail(dot)com>
>>
>> guys,
>> trying multiple updates without success like so:
>>
>> update  a
>> set             number = any (  select number from b where
>> a.number=b.number)
>> where   number is null;
>> >> 'syntax error at or near "any"'
>>
>> subquery returns more than one row. using 8.1.
>> thks, jzs
>>
>
> If the subquery returns multiple values, which value do you want to set to
> the 'number' column. For each record there can be only one value for this
> column?
> The subquery has to return only one value such a query.
> Show the tables structure, maybe there is a better column than the 'number'
> to join the 'a' and 'b' tables.
> regards
> Szymon Guz