June 29, 2009 at 3:05 pm
BOL is the "help" for the server. It may have been installed with the server. If you can't find it, MS helpfully keeps it online.
Here's the URL for the online 2008 version for downloading
If you search the MS site, the other versions are also available.
June 29, 2009 at 3:13 pm
Seems there are actually two or three errors in your code.
First: The error you posted (the less important):
The sub-query seems to return more than one row to take as UPDATE value for AVERAGE_SALE_COUNT
You can this in two different ways:
update sales_contest_salesman_dan
---===================================================
-- use "TOP(1)" to get only one row by your sub-query
update sales_contest_salesman_dan
set AVERAGE_SALE_COUNT =
(
select TOP(1) written_sales_count
from dw_historical_monthly_salesman_sales as hmss, sales_contests sc
where sc.contest_id = 11 and
hmss.commission_month between '5-1-09' and '6-30-09' and
hmss.employee_code = sales_contest_salesman_dan.employee_code
)
---===================================================
-- use AVG/MIN/MAX/SUM/... to get an aggregated result
update sales_contest_salesman_dan
set AVERAGE_SALE_COUNT =
(
select AVG(written_sales_count)
from dw_historical_monthly_salesman_sales as hmss, sales_contests sc
where sc.contest_id = 11 and
hmss.commission_month between '5-1-09' and '6-30-09' and
hmss.employee_code = sales_contest_salesman_dan.employee_code
)
Second: Possible data corruption (most important):
If your sub-query is not missing an aggregation your data relation between "sales_contest_salesman_dan" and "dw_historical_monthly_salesman_sales" seems to be invalid.
Third: Performance problems (becomes more and more important depending on the count of data):
Your sub-query become executed for each row in your "sales_contest_salesman_dan" table As Gail and Lynn wrote, there is another UPDATE syntax which enables you to use a JOIN between your tables. Since you probably need a aggregation and you are not allowed to use GROUP BY directly within your UPDATE clause you can use a CTE to group your data and JOIN them to your destination table.
;
WITH cte AS
(
select
AVG(written_sales_count) avg_count,
hmss.employee_code
from dw_historical_monthly_salesman_sales as hmss, sales_contests sc
where sc.contest_id = 11 and
hmss.commission_month between '5-1-09' and '6-30-09'
group by hmss.employee_code
)
update scsd SET
scsd.AVERAGE_SALE_COUNT = cte.avg_count
FROM sales_contest_salesman_dan scsd
JOIN cte ON scsd.employee_code = cte.employee_code
Since you didn't provide any DDL and/or test data, all statements I posted are complete un-checked. Since you didn't explain your required result on those data I don't know if you need an aggregation or not. That's the reason why we need test data 😉
Flo
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply