Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 UPDATE не работает  [new]
sanek89689
Guest
такой рабочий 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 madd 

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]


Что тут поменять в UPDATE?

update dbo.aaa_ALL_Measure_level_additional_info

set 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 madd 

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]
19 янв 13, 03:05    [13795316]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE не работает  [new]
sanek89689
Guest
Такая ошибка:

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'.
19 янв 13, 03:11    [13795319]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE не работает  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
set Measure_ID= mm.Id --Measure_ID
19 янв 13, 04:09    [13795364]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE не работает  [new]
Гость333
Member

Откуда:
Сообщений: 3683
sanek89689
такой рабочий SELECT:

select distinct mm.Id Measure_ID
FROM  
...
group by  p.id_sf  
,mm.Id  
,madd.[Measure]
,madd.[Current_Measure_Stage]

Это странный селект. Вы группируете данные по 4 полям, а потом оставляете одно поле, к которому применяется DISTINCT. Зачем?

sanek89689
Что тут поменять в UPDATE?

update dbo.aaa_ALL_Measure_level_additional_info
set Measure_ID= mm.Id Measure_ID
FROM  
  (
     SELECT distinct
             ...
             ,sum(isnull(Cast( [Incentive_Amount] AS FLOAT), 0)) [Incentive_Amount] (2)
     FROM  dbo.aaa_ALL_Measure_level_additional_info madd 
     group by ... 
  ) madd
  ...
  left join Measure__c mm
    on ...
      isnull(madd.Incentive_Amount, 0) = isnull(mm.Amount_of_Measure_Incentive__c, 0) (2)
      ...
group by ... (1)

1) GROUP BY нельзя применять в команде UPDATE;
2) Вы сначала суммируете Incentive_Amount, а потом хотите разложить его обратно на слагаемые? Или в чём смысл выделенного?
20 янв 13, 00:25    [13797573]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить