Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help With Update Statement 1

Status
Not open for further replies.
allyne

allyne

MIS
Feb 9, 2001
410
US
Hi Everyone,

I need to create an update statement that looks like the following:
UPDATE tblFinalOneLine063001
SET
[tblFinalOneLine063001].[Current Month Total MOUs] =
Sum (convert(decimal,([IcScorecardData063001].[MOU]))),
[tblFinalOneLine063001].[Current Month Home Area MOUs]=
Sum((case
When [IcScorecardData063001].[HOME] is not null
then Convert(decimal,([IcScorecardData063001].[MOU]))
ELSE 0
end)),
[tblFinalOneLine063001].[Current Month Roam MOUs] =
Sum((case
when [IcScorecardData063001].[HOME] is null
then Convert(decimal,([IcScorecardData063001].[MOU]))
ELSE 0
end)),
[tblFinalOneLine063001].[Current Month Roam MOU %] =
Sum((case
when [IcScorecardData063001].[HOME] is null
then Convert(decimal,([IcScorecardData063001].[MOU]))
ELSE 0
end))/Sum (Convert(decimal,([IcScorecardData063001].[MOU])))

FROM [IcScorecardData063001]
LEFT JOIN [VolActiveSubs063001] ON [IcScorecardData063001].[MIN] = [VolActiveSubs063001].[Telephone]
INNER JOIN [tblFinalOneLine063001] ON [IcScorecardData063001].[MIN] = [tblFinalOneLine063001].[MIN]

GROUP BY [IcScorecardData063001].[MIN]

Having
Sum (convert(decimal,([IcScorecardData063001].[MOU]))) >200 AND
Sum((case
when [IcScorecardData063001].[HOME] is null
then Convert(decimal,([IcScorecardData063001].[MOU]))
ELSE 0
end))/Sum (Convert(decimal,([IcScorecardData063001].[MOU]))) > 0.7

The error message I receive is "An aggregate may not appear in the set list of an UPDATE statement"

Can someone show me the correct syntax to use in this type of an update statement.

Thanks so much for your help!
Cathy

 
Sort by date Sort by votes

Cathy,

I've attempted to rewrite the query. In doing so, I question the need for the join to VolActiveSubs063001. You never reference any column in that table except in the join statement. Therefore, I removed it in the query that follows. If I missed something, please forgive me.

What I've done with the query is to create a sub-query for summarizing the data in IcScorecardData063001. The summary data is then used to update tblFinalOneLine063001. Hope this works for you. I have no way to test it.

NOTE: I also used aliases for the table names to simplify and increase readability.

[tt]UPDATE tblFinalOneLine063001
SET
[Current Month Total MOUs] = b.SumMou,
[Current Month Home Area MOUs]= b.SumHomeMou,
[Current Month Roam MOUs] = b.SumRoamMou,
[Current Month Roam MOU %] = b.SumRoamMouPerc,

FROM [tblFinalOneLine063001] a Inner Join
(Select [Min],
Sum (convert(decimal,([MOU]))) As SumMou,
Sum((case
When [HOME] is not null
Then Convert(decimal,([MOU]))
ELSE 0 End)) As SumHomeMou,
Sum((case
When [HOME] is null
Then Convert(decimal,([MOU]))
ELSE 0 End)) As SumRoamMou,
Sum((case
When [HOME] is null
Then Convert(decimal,([MOU]))
ELSE 0 end))/Sum(Convert(decimal,([MOU]))) As SumRoamMouPerc

FROM [IcScorecardData063001] b

GROUP BY [MIN]

HAVING Sum (convert(decimal,([MOU]))) > 200
AND Sum((case
When [HOME] is null
Then Convert(decimal,([MOU]))
ELSE 0 End))/Sum (Convert(decimal,([MOU]))) > 0.7) As b

ON a.[MIN] = b.[MIN][/tt]
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin
 
Upvote 0 Downvote
Hi Terry,

Thanks for the quick response. I will give it a try. Thanks for catching the extra table. This is part of a large select query that is being used to revamp an old process. I'll let you know if this works. Learning something new each day!

Thanks Again!
Cathy
 
Upvote 0 Downvote
Hi Terry

I just tried your query. I'm getting an error message that states "The column prefix 'b' does not match with a table name or alias name used in the query."

Any suggestions?

Thanks again
Cathy
 
Upvote 0 Downvote

Sorry! :-( I made an error on the following line of the query. Remove the b at the end of the line.

FROM [IcScorecardData063001] b

Hopefully, that is the only problem. ;-) Let me know. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin
 
Upvote 0 Downvote
I come up with the same error message. I also tried removing that 'as b' and adding back the [IcscorecardDate063001] b but still come up with the same error message.....
 
Upvote 0 Downvote

I did a little more cleanup. There was an extra comma. Hope it works now.

UPDATE tblFinalOneLine063001
SET
[Current Month Total MOUs] = b.SumMou,
[Current Month Home Area MOUs]= b.SumHomeMou,
[Current Month Roam MOUs] = b.SumRoamMou,
[Current Month Roam MOU %] = b.SumRoamMouPerc

FROM [tblFinalOneLine063001] a Inner Join
(Select [Min],
Sum(convert(decimal,[MOU])) As SumMou,
Sum(case
When [HOME] is not null
Then Convert(decimal,[MOU])
ELSE 0 End) As SumHomeMou,
Sum(case
When [HOME] is null
Then Convert(decimal,[MOU])
ELSE 0 End) As SumRoamMou,
Sum(case
When [HOME] is null
Then Convert(decimal,[MOU])
ELSE 0 end)/Sum(Convert(decimal,[MOU])) As SumRoamMouPerc

FROM [IcScorecardData063001]

GROUP BY [MIN]

HAVING Sum(convert(decimal,[MOU])) > 200
AND Sum(case
When [HOME] is null
Then Convert(decimal,[MOU])
ELSE 0 End)/Sum(Convert(decimal,[MOU])) > 0.7) As b

ON a.[MIN] = b.[MIN] Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin
 
Upvote 0 Downvote

Does the error message tell which line is in error? Please post your query and the message.

Thanks, Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin
 
Upvote 0 Downvote
The error messages I receive is:
Server: Msg 107, Level16, State2, Line1
"The column prefix 'b' does not match with a table name or alias name used in the query"

Here is the query:UPDATE tblFinalOneLine063001
SET
[Current Month Total MOUs] = b.SumMou,
[Current Month Home Area MOUs]= b.SumHomeMou,
[Current Month Roam MOUs] = b.SumRoamMou,
[Current Month Roam MOU %] = b.SumRoamMouPerc

FROM [tblFinalOneLine063001] a Inner Join
(Select [Min],
Sum(convert(decimal,[MOU])) As SumMou,
Sum(case
When [HOME] is not null
Then Convert(decimal,[MOU])
ELSE 0 End) As SumHomeMou,
Sum(case
When [HOME] is null
Then Convert(decimal,[MOU])
ELSE 0 End) As SumRoamMou,
Sum(case
When [HOME] is null
Then Convert(decimal,[MOU])
ELSE 0 end)/Sum(Convert(decimal,[MOU])) As SumRoamMouPerc

FROM [IcScorecardData063001]

GROUP BY [MIN]

HAVING Sum(convert(decimal,[MOU])) > 200
AND Sum(case
When [HOME] is null
Then Convert(decimal,[MOU])
ELSE 0 End)/Sum(Convert(decimal,[MOU])) > 0.7) As b

ON a.[MIN] = b.[MIN]

 
Upvote 0 Downvote

I finally got it! You are running SQL 7.0 and I'm running SQL 2000. Duh! Sorry for the run around.

Create a view to summarize the data. Use the view in the update query.
[tt]
Create View vSumamrizeData As
Select [Min],
Sum(convert(decimal,[MOU])) As SumMou,
Sum(case
When [HOME] is not null
Then Convert(decimal,[MOU])
ELSE 0 End) As SumHomeMou,
Sum(case
When [HOME] is null
Then Convert(decimal,[MOU])
ELSE 0 End) As SumRoamMou,
Sum(case
When [HOME] is null
Then Convert(decimal,[MOU])
ELSE 0 end)/Sum(Convert(decimal,[MOU])) As SumRoamMouPerc

FROM [IcScorecardData063001]

GROUP BY [MIN]

HAVING Sum(convert(decimal,[MOU])) > 200
AND Sum(case
When [HOME] is null
Then Convert(decimal,[MOU])
ELSE 0 End)/Sum(Convert(decimal,[MOU])) > 0.7
Go

UPDATE tblFinalOneLine063001
SET
[Current Month Total MOUs] = b.SumMou,
[Current Month Home Area MOUs]= b.SumHomeMou,
[Current Month Roam MOUs] = b.SumRoamMou,
[Current Month Roam MOU %] = b.SumRoamMouPerc

FROM [tblFinalOneLine063001] a Inner Join vSumamrizeData b
ON a.[MIN] = b.[MIN] [/tt] Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin
 
Upvote 0 Downvote
Thanks! I'll give it a try. Sorry! :( I should have specified what version I was using. I even read your FAQ and still didn't type it with my question.

Thanks for all your help.

Cathy
 
Upvote 0 Downvote
Hi Terry,

Thanks so much for all your time and effort. It works great!

Cathy
 
Upvote 0 Downvote

What? No star! ;-) Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin
 
Upvote 0 Downvote
Hi Terry,

I tried to insert a GOLD star into my text above but it wouldn't work. I think you deserve a GOLD metal for this one!

Thanks again for all your effor and help. I didn't know that you can create a view in a stored procedure that uses case statements. This will help me alot in the future!

Thanks!
Cathy
 
Upvote 0 Downvote

FYI: You insert Stars by clicking on[ul]Let username know
this post was helpful![/ul]

Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
2K
mikrom
mikrom
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
702
f0rg3tfu1
f0rg3tfu1
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
1K
gmmastros
gmmastros
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
2K
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
2K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top