Re: update ... set ... subquery

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!