Обсуждение: update ... set ... subquery

Поиск
Список
Период
Сортировка

update ... set ... subquery

От
John Smith
Дата:
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

Re: update ... set ... subquery

От
Szymon Guz
Дата:

2010/3/17 John Smith <jayzee.smith@gmail.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

Re: update ... set ... subquery

От
hubert depesz lubaczewski
Дата:
On Wed, Mar 17, 2010 at 03:13:26PM -0400, John Smith wrote:
> 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

make is: number in ( select number from ...

number = any () is syntax for arrays.

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Re: update ... set ... subquery

От
John Smith
Дата:
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@gmail.com> wrote:
>
> 2010/3/17 John Smith <jayzee.smith@gmail.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

Re: update ... set ... subquery

От
Alban Hertroys
Дата:
On 17 Mar 2010, at 21:40, John Smith wrote:

> 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;
>

Looks like you want:
update a
set number = b.number
from b
where number is null
and name = b.name;

> 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

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4ba1607e10411836611115!