"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:3f798cee$0$8767$ed9e5944@reading.news.pipex.net...
> 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
> >
> >
>
>

"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
>
>

"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.net...
> > 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
> > >
> > >
> >
> >
>
>