Re: SQL update question

Kamal wrote:
> Hi everybody.
>
> If I have a table like this:
>
> col0(number) cnt (number)
>
> 0 (null)
> 0 (null)
> 0 (null)
> 1 (null)
> 1 (null)
> 2 (null)
>
> And I want:
>
> col0(number) cnt (number)
>
> 0 3
> 0 3
> 0 3
> 1 2
> 1 2
> 2 1
>
> where cnt is the count of the records with different values in col0:
> what would be the fastest single update to achieve this?
>
> I would do
>
> update t t1 set t1.cnt = (select count(*) from t t2
> where t1.col0 = t2.col0
> group by t2.col0);
>
> Is there a better way?
> I know there's a redundancy but I want it.
>
> Thank you.
>
> Kamal

You can try this:
UPDATE (SELECT count(*) OVER (partition by col0) as ncnt,

                cnt
         FROM T) AS T1
      SET cnt = ncnt;

I know Oracle supports the query as target. I don't know whether it will tolerate the OLAP in the target, ...

Cheers
Serge

PS: A materialized view holding the count would be a lot easier to maintain. Received on Mon Nov 22 2004 - 10:09:09 CST