That's no valid syntax for SqlServer :-((
"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message
news:41ec4eb4_at_news.victoria.tc.ca...
> Jan van Veldhuizen (janvv_at_xs4all.nl) wrote:
> : I have an update that updates two columns in a join.:
>
> : update table1
> : set col1 = (select avg(colx)
> : from table2
> : where table2.id = table1.id
> : group by table2.id),
> : set col2 = (select avg(coly)
> : from table2
> : where table2.id = table1.id
> : group by table2.id)
>
> : This looks inefficient because the select on table2 is done twice.
>
> : Can this be done smarter?
>
> : Nota bene: the sql statement must be compatible with both SqlServer and
> : Oracle.
>
> no idea about sqlserver, but in Oracle you can say things like
>
> SET (x,y) = ( select 1,2 from dual )
>
> no idea whether this makes any difference to efficiency.
>
>
> --
>
> This space not for rent.
Received on Tue Jan 18 2005 - 05:15:11 CST
