update joined columns

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. Received on Mon Jan 17 2005 - 17:20:50 CST