Re: update joined columns

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.