Data Warehouse UPDATE doesnt work
-
Friday, January 18, 2013 11:14 PM
I have this SELECT:
select distinct mm.Id Measure_ID
FROM
(
SELECT distinct
[Building],
case
when [Measure]='Number of Showerheads Replaced' then 'consolidated three measure'
when [Measure]='Number of Kitchen Aerators Replaced' then 'consolidated three measure'
when [Measure]='Number of Bath Faucet Aerators Replaced' then 'consolidated three measure'
else [Measure] end [Measure]
,[Current_Measure_Stage]
,sum(isnull(Cast( [Incentive_Amount] AS FLOAT), 0)) [Incentive_Amount]
FROM dbo.aaa_ALL_Measure_level_additional_info ggg
group by [Current_Measure_Stage],
[Building],
case
when [Measure]='Number of Showerheads Replaced' then 'consolidated three measure'
when [Measure]='Number of Kitchen Aerators Replaced' then 'consolidated three measure'
when [Measure]='Number of Bath Faucet Aerators Replaced' then 'consolidated three measure'
else [Measure] end
) madd
left join dbo.aaa_ALL_Project_level_01142013 p on UPPER(LTRIM(RTRIM(madd.Building)))=UPPER(LTRIM(RTRIM(p.Building_RUTH)))
left join Measure__c mm
on LTRIM(RTRIM(p.id_sf)) = LTRIM(RTRIM(mm.Building_Project__c)) -- 1622
and isnull(madd.Incentive_Amount, 0) = isnull(mm.Amount_of_Measure_Incentive__c, 0)
and mm.Program_Year_Identifier__c='PY 2012'
and ( mm.Date_Acquired__c<>'2012-12-01 00:00:00.0000000' or mm.Date_Acquired__c is not null )
group by p.id_sf
,mm.Id
,madd.[Measure]
,madd.[Current_Measure_Stage]Why this UPDATE doesnt work?
update ggg
set ggg.Measure_ID= mm.Id Measure_ID
FROM
(
SELECT distinct
[Building],
case
when [Measure]='Number of Showerheads Replaced' then 'consolidated three measure'
when [Measure]='Number of Kitchen Aerators Replaced' then 'consolidated three measure'
when [Measure]='Number of Bath Faucet Aerators Replaced' then 'consolidated three measure'
else [Measure] end [Measure]
,[Current_Measure_Stage]
,sum(isnull(Cast( [Incentive_Amount] AS FLOAT), 0)) [Incentive_Amount]
FROM dbo.aaa_ALL_Measure_level_additional_info ggg
group by [Current_Measure_Stage],
[Building],
case
when [Measure]='Number of Showerheads Replaced' then 'consolidated three measure'
when [Measure]='Number of Kitchen Aerators Replaced' then 'consolidated three measure'
when [Measure]='Number of Bath Faucet Aerators Replaced' then 'consolidated three measure'
else [Measure] end
) madd
left join dbo.aaa_ALL_Project_level_01142013 p on UPPER(LTRIM(RTRIM(madd.Building)))=UPPER(LTRIM(RTRIM(p.Building_RUTH)))
left join Measure__c mm
on LTRIM(RTRIM(p.id_sf)) = LTRIM(RTRIM(mm.Building_Project__c)) -- 1622
and isnull(madd.Incentive_Amount, 0) = isnull(mm.Amount_of_Measure_Incentive__c, 0)
and mm.Program_Year_Identifier__c='PY 2012'
and ( mm.Date_Acquired__c<>'2012-12-01 00:00:00.0000000' or mm.Date_Acquired__c is not null )
group by p.id_sf
,mm.Id
,madd.[Measure]
,madd.[Current_Measure_Stage]
Error:
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'Measure_ID'. Msg 102, Level 15, State 1, Line 28 Incorrect syntax near 'madd'.
Thanks
- Edited by solo2012 Friday, January 18, 2013 11:26 PM
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Monday, January 28, 2013 10:10 PM Better title.
All Replies
-
Friday, January 18, 2013 11:26 PM
To avoid the first error, try to remove the Measure_ID while you are updating with mm.Id. I think you used the same SELECT statement and included the ALIAS of mm.Id as Measure_ID.
update dbo.aaa_ALL_Measure_level_additional_info
set Measure_ID= mm.Id
Narsimha
- Edited by Naarasimha Friday, January 18, 2013 11:27 PM
- Proposed As Answer by Iric WenModerator Monday, January 28, 2013 6:02 AM
- Unproposed As Answer by Kalman TothMicrosoft Community Contributor, Moderator Monday, January 28, 2013 10:09 PM
-
Friday, January 18, 2013 11:30 PM
oops! yes, forgot to remove it
But now other error:
Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'group'.update ggg set ggg.Measure_ID= mm.Id FROM ( SELECT distinct [Building], case when [Measure]='Number of Showerheads Replaced' then 'consolidated three measure' when [Measure]='Number of Kitchen Aerators Replaced' then 'consolidated three measure' when [Measure]='Number of Bath Faucet Aerators Replaced' then 'consolidated three measure' else [Measure] end [Measure] ,[Current_Measure_Stage] ,sum(isnull(Cast( [Incentive_Amount] AS FLOAT), 0)) [Incentive_Amount] FROM dbo.aaa_ALL_Measure_level_additional_info ggg group by [Current_Measure_Stage], [Building], case when [Measure]='Number of Showerheads Replaced' then 'consolidated three measure' when [Measure]='Number of Kitchen Aerators Replaced' then 'consolidated three measure' when [Measure]='Number of Bath Faucet Aerators Replaced' then 'consolidated three measure' else [Measure] end ) madd left join dbo.aaa_ALL_Project_level_01142013 p on UPPER(LTRIM(RTRIM(madd.Building)))=UPPER(LTRIM(RTRIM(p.Building_RUTH))) left join Measure__c mm on LTRIM(RTRIM(p.id_sf)) = LTRIM(RTRIM(mm.Building_Project__c)) -- 1622 and isnull(madd.Incentive_Amount, 0) = isnull(mm.Amount_of_Measure_Incentive__c, 0) and mm.Program_Year_Identifier__c='PY 2012' and ( mm.Date_Acquired__c<>'2012-12-01 00:00:00.0000000' or mm.Date_Acquired__c is not null ) group by p.id_sf ,mm.Id ,madd.[Measure] ,madd.[Current_Measure_Stage]
-
Friday, January 18, 2013 11:35 PM
If I remove GROUP BY then
ggg.Measure_ID updated to same value
-
Monday, January 28, 2013 10:09 PMModeratorAny progress?
Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
.png)
