Many fields update from a Group by Clause

Hi All,

In Oracle, I can easily make this query :

UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'

I cannot seem to be able to do the same thing with MS-SQL. There are
only 2 ways I've figured out, and I fear performance cost in both cases,
which are these :
1)
UPDATE t1 SET f1=(SELECT AVG(f3)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'

and then the same statement but with f2, and

2)
UPDATE t1 SET f1=(SELECT AVG(f3)
FROM t2
WHERE t2.f5=t1.f6),
f2=(SELECT SUM(f4)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'

Is there a way with MS-SQL to do the Oracle equivalent in this case ?

Thanks,

MichelHi

You could try something like

UPDATE t
SET f1 = dt.avgcol, f2 = dt.sumcol
FROM t1 JOIN ( SELECT f5, AVG(f3) AS avgcol, SUM(f4) AS SumCol
FROM t2
WHERE f5 = 'Something'
GROUP BY f5 ) dt ON dt.f5=t1.f6

John
"Michel" <Michel@.askme.com> wrote in message
news:mGfeb.1763$r.377054@.news20.bellglobal.com...
> Hi All,
> In Oracle, I can easily make this query :
> UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
> FROM t2
> WHERE t2.f5=t1.f6)
> WHERE f5='Something'
> I cannot seem to be able to do the same thing with MS-SQL. There are
> only 2 ways I've figured out, and I fear performance cost in both cases,
> which are these :
> 1)
> UPDATE t1 SET f1=(SELECT AVG(f3)
> FROM t2
> WHERE t2.f5=t1.f6)
> WHERE f5='Something'
> and then the same statement but with f2, and
> 2)
> UPDATE t1 SET f1=(SELECT AVG(f3)
> FROM t2
> WHERE t2.f5=t1.f6),
> f2=(SELECT SUM(f4)
> FROM t2
> WHERE t2.f5=t1.f6)
> WHERE f5='Something'
> Is there a way with MS-SQL to do the Oracle equivalent in this case ?
> Thanks,
> Michel|||It works fine, thank you very much. I did try something similar just before,
but I made the links in the where clause instead of the joins, and for some
reason it wasn't working.

Well, thanks, now it works fine and I'm saving 50% time on the queries!

Michel

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f798cee$0$8767$ed9e5944@.reading.news.pipex.n et...
> Hi
> You could try something like
> UPDATE t
> SET f1 = dt.avgcol, f2 = dt.sumcol
> FROM t1 JOIN ( SELECT f5, AVG(f3) AS avgcol, SUM(f4) AS SumCol
> FROM t2
> WHERE f5 = 'Something'
> GROUP BY f5 ) dt ON
dt.f5=t1.f6
> John
> "Michel" <Michel@.askme.com> wrote in message
> news:mGfeb.1763$r.377054@.news20.bellglobal.com...
> > Hi All,
> > In Oracle, I can easily make this query :
> > UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
> > FROM t2
> > WHERE t2.f5=t1.f6)
> > WHERE f5='Something'
> > I cannot seem to be able to do the same thing with MS-SQL. There are
> > only 2 ways I've figured out, and I fear performance cost in both cases,
> > which are these :
> > 1)
> > UPDATE t1 SET f1=(SELECT AVG(f3)
> > FROM t2
> > WHERE t2.f5=t1.f6)
> > WHERE f5='Something'
> > and then the same statement but with f2, and
> > 2)
> > UPDATE t1 SET f1=(SELECT AVG(f3)
> > FROM t2
> > WHERE t2.f5=t1.f6),
> > f2=(SELECT SUM(f4)
> > FROM t2
> > WHERE t2.f5=t1.f6)
> > WHERE f5='Something'
> > Is there a way with MS-SQL to do the Oracle equivalent in this case ?
> > Thanks,
> > Michel|||Is that 50% over Oracle :)

"Michel" <Michel@.askme.com> wrote in message
news:0Bgeb.1931$r.386539@.news20.bellglobal.com...
> It works fine, thank you very much. I did try something similar just
before,
> but I made the links in the where clause instead of the joins, and for
some
> reason it wasn't working.
> Well, thanks, now it works fine and I'm saving 50% time on the queries!
> Michel
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3f798cee$0$8767$ed9e5944@.reading.news.pipex.n et...
> > Hi
> > You could try something like
> > UPDATE t
> > SET f1 = dt.avgcol, f2 = dt.sumcol
> > FROM t1 JOIN ( SELECT f5, AVG(f3) AS avgcol, SUM(f4) AS SumCol
> > FROM t2
> > WHERE f5 = 'Something'
> > GROUP BY f5 ) dt ON
> dt.f5=t1.f6
> > John
> > "Michel" <Michel@.askme.com> wrote in message
> > news:mGfeb.1763$r.377054@.news20.bellglobal.com...
> > > Hi All,
> > > > In Oracle, I can easily make this query :
> > > > UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
> > > FROM t2
> > > WHERE t2.f5=t1.f6)
> > > WHERE f5='Something'
> > > > I cannot seem to be able to do the same thing with MS-SQL. There
are
> > > only 2 ways I've figured out, and I fear performance cost in both
cases,
> > > which are these :
> > > 1)
> > > UPDATE t1 SET f1=(SELECT AVG(f3)
> > > FROM t2
> > > WHERE t2.f5=t1.f6)
> > > WHERE f5='Something'
> > > > and then the same statement but with f2, and
> > > > 2)
> > > UPDATE t1 SET f1=(SELECT AVG(f3)
> > > FROM t2
> > > WHERE t2.f5=t1.f6),
> > > f2=(SELECT SUM(f4)
> > > FROM t2
> > > WHERE t2.f5=t1.f6)
> > > WHERE f5='Something'
> > > > Is there a way with MS-SQL to do the Oracle equivalent in this case ?
> > > > Thanks,
> > > > Michel
> >