UPDATE statement with COUNT( ) as an ANALYTIC

I want to update a column with the count of rows which share a common value.

I'd like to be able to do it with count( ) as an analytic ... [9.2 & 10.2]

create table dropme
as select 0 n, object_type
from all_objects
where rownum < 1000
/

update dropme
set n = count(*) over (partition by object_type)
/

But I get...

ORA-00934: group function is not allowed here

Surely what I'm asking is sensible. Why can I not do it?

Is there any way to do this in SQL which scans the table only once?

Thanks,
John Received on Fri Aug 10 2007 - 05:34:24 CDT