Aggregate statement in Update table statement

[Queryman]

Is there another way to do this query.

update tempdb.MD_CLAIMS set AWP_PCT = (AWP/SUM(AWP)



In this instance I want to divide a number by it's total in the whole table to obtain a percentage.

When I run the query as is, I get a message that I am performing an illegal aggregate operation in a set statement.

Michael

 

[Queryman]

I think I need to create views where the aggregates are done and then update.

Michael

 

[r937]

if you were to do this in a SELECT, it would look like this --

select AWP
, AWP /
( select sum(AWP)
from tempdb.MD_CLAIMS ) as AWP_PCT
from tempdb.MD_CLAIMS

i would suggest that you not carry the calculated percentage as a separate column -- there's no need, and you would have to re-update the entire table every time you add a new row

but if you simply must carry the AWP_PCT column, i think (no guarantees, i did not test) you do it like this --

update tempdb.MD_CLAIMS
set AWP_PCT = AWP /
( select sum(AWP)
from tempdb.MD_CLAIMS )

rudy

 

[Queryman]

Rudy,
Thanks for your suggestion, but I think the syntax you suggested is not working for me.

Michael

 

[r937]

why do you think that?

did you get an error message, or did it not update any rows, or did it update the rows with the wrong amount?

by the way, what database are you using? perhaps if you posted your qustion in the forum for that database, you'd get a customized answer...

rudy

 

[Queryman]

Got an error message, database is Teradata, I took your suggestions and posted my question there.
Thanks

Michael