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 3 14868
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" <Mi****@askme.com> wrote in message
news:mG*****************@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" <jb************@hotmail.com> wrote in message
news:3f**********************@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" <Mi****@askme.com> wrote in message news:mG*****************@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" <Mi****@askme.com> wrote in message
news:0B*****************@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" <jb************@hotmail.com> wrote in message news:3f**********************@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" <Mi****@askme.com> wrote in message news:mG*****************@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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Dave |
last post by:
I have 2 tables, one with names, and another with addresses, joined by their
CIVICID number (unique to the ADDRESSINFO table) in Oracle.
I need to update a field in the NAMEINFO table for a...
|
by: Lauren Quantrell |
last post by:
In VBA, I constructed the following to update all records in
tblmyTable with each records in tblmyTableTEMP having the same
UniqueID:
UPDATE
tblMyTable RIGHT JOIN tblMyTableTEMP ON...
|
by: kattukuyil |
last post by:
Guys, this is what I want to do. How can I do this ?
Select employid,
Gross=( Select sum (uprtrxam) from Fleet..upr30300 where pyrlrtyp=1),
DedofWages=( Select sum (uprtrxam) from...
|
by: kalamos |
last post by:
This statement fails
update ded_temp a
set a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by...
|
by: serge |
last post by:
/*
This is a long post. You can paste the whole message
in the SQL Query Analyzer.
I have a scenario where there are records
with values pointing to wrong records and I need to fix them
using an...
|
by: Dan |
last post by:
(Using Classic ASP & MS Access)
I have a page that has 120 fields on it (mostly checkboxes). I cannot
split this into smaller pages.
So what i want to do is write a class that handles this. in...
|
by: Ryan |
last post by:
I'm learning when it comes to Datasets. Should I just have a single dataset
to handle my entire application data or should I use multiple - a dataset
for each function or form within my...
|
by: P3Eddie |
last post by:
Hello all!
I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same.
My problem may be a simple find duplicates / do something...
|
by: Icemokka |
last post by:
Hi,
I've got a table in MsAccess with 100+ fields.
If I fill a tabletable with this table , change some values , get the
update-command via commandbuilder , the update fails.
This because the...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |