SQL update question

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 Received on Mon Nov 22 2004 - 09:18:45 CST