RE: Help Need

[Cale, Rick T (Richard)]

Title: Help Need

Are all the columns in user repeated? I doubt it because you would just remove duplicate records.  I am assuming userid is key field.

You could try

 

CREATE TABLE usercheck AS SELECT * FROM user;

ALTER TABLE usercheck ADD(repeated NUMBER);

UPDATE usercheck a

SET repeated = (SELECT COUNT(*) - 1

                         FROM user b

                         WHERE b.userid=a.userid

                         GROUP BY userid

                         HAVING COUNT(*) > 1);

 

Rick

From: Deewaker G.V. [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 02, 2001 7:40 AM
To: Multiple recipients of list ORACLE-L
Subject: Help Need

Hi DBA Gurus,

I have a table called user..

It has userid, firstname, lastname, city, address, telephone, email

There are some userid which have been reentered .. I would like to insert a column called repeats and take the number of times the userid is repeated

How to I go about it

Create table usercheck as (userid, firstname, repeated,lastname, city, address, telephone, email)
As
Select userid, firstname, count(*) userid as repeated,,lastname, city, address, telephone, email)
From user;

Something like this..

For example the userid of Deewaker is 123123 and he as been inserted 5 times in the table USER the Repeated should give a count of 4.

Please help me.

with warm regards,

Deewaker G. V.

Baazee.com India Pvt. Ltd.
(: 4611323 Extn: 216
Fax : 4611324

<<Deewaker G.V..vcf>>