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

One possible solution is:

UPDATE (
   SELECT col0, cnt
   FROM t) p
SET (p.col0, p.cnt) = (

    select col0, COUNT(*) CNT
    from t
    where p.col0 = col0
    group by col0);

Of course the plans as generated by DBMS_XPLAN are identical. At least on my machine. So not better ... just different.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)