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!