duplicate records

Hi ..
i am trying to update a table in foxpro 6.0 where if field contents
any duplictaed
entries than one of the field should be updated with random number
which is unique so i can make all entries unique
i searched a lot but couldn't find any solution which i could
understand easily .
is it very difficult in sql to update duplicate entries with new
unique random values?
table example : id is primary key
----------------------
id | name | age
----------------------
1 a 24
2 b 24
3 c 28
4 d 12
5 e 12
in the above table id is unique but the age is same for a , b and
d,e . what i am trying to do is that in a single query i can update
table set age=age+random unique number where duplicate age exists .
that mean b will become 24+some random number and e will be 12+some
random number

hope someone reply's soon
thanks

Anders
Mon Jul 14 16:10:18 CDT 2008


"beginer" <deepakgc@gmail.com> wrote in message
news:7afcfad9-ddb5-45ab-bd94-f260ff4adbd8@k30g2000hse.googlegroups.com...
> Hi ..
> i am trying to update a table in foxpro 6.0 where if field contents
> any duplictaed
> entries than one of the field should be updated with random number
> which is unique so i can make all entries unique
> i searched a lot but couldn't find any solution which i could
> understand easily .
> is it very difficult in sql to update duplicate entries with new
> unique random values?
> table example : id is primary key
> ----------------------
> id | name | age
> ----------------------
> 1 a 24
> 2 b 24
> 3 c 28
> 4 d 12
> 5 e 12
> in the above table id is unique but the age is same for a , b and
> d,e . what i am trying to do is that in a single query i can update
> table set age=age+random unique number where duplicate age exists .
> that mean b will become 24+some random number and e will be 12+some
> random number
>
> hope someone reply's soon
> thanks

May I answer with a question? Why?

-Anders

Bernhard
Tue Jul 15 04:45:18 CDT 2008

Hi beginer,

> i am trying to update a table in foxpro 6.0 where if field contents
> any duplictaed
> entries than one of the field should be updated with random number
> which is unique so i can make all entries unique
> i searched a lot but couldn't find any solution which i could
> understand easily .
> is it very difficult in sql to update duplicate entries with new
> unique random values?
> table example : id is primary key
> ----------------------
> id | name | age
> ----------------------
> 1 a 24
> 2 b 24
> 3 c 28
> 4 d 12
> 5 e 12
> in the above table id is unique but the age is same for a , b and
> d,e . what i am trying to do is that in a single query i can update
> table set age=age+random unique number where duplicate age exists .
> that mean b will become 24+some random number and e will be 12+some
> random number

With Fox 6 it might be very hard to impossible since its support of subqueries
is not that good.
But I don't understand your approach.
If you add a random number to the age, you might get a duplicate to some other
record's age.
To distinguish the original double ages, simply use the ID field, so there is no
need to manipulate the age.

Regards
Bernhard Sander

beginer
Wed Jul 16 02:30:28 CDT 2008

Hi Bernhard ,

i made the table only to explain my problem..
the real problem is that i have to remove the duplicate records where
people have mistakenly stored similar numbers which they were not
suppose to do .. but now there are lots of duplicate entries and found
out yesterday that there is no primary key field .. as you said it
might be hard to impossible . the only thing i can think of is
exporting the fox 6 db into mysql ( where i still need to figure out
how to do this ) and after making the changes there ...import it back
to fox 6. but if you have any more suggestions or if you can guide me
to the right direction .. i will be very thankful

thanks a lot for your previous reply

beginer

Bernhard
Wed Jul 16 06:09:05 CDT 2008

Hi beginer,

> the real problem is that i have to remove the duplicate records where
> people have mistakenly stored similar numbers which they were not
> suppose to do .. but now there are lots of duplicate entries and found
> out yesterday that there is no primary key field .. as you said it
> might be hard to impossible . the only thing i can think of is
> exporting the fox 6 db into mysql ( where i still need to figure out
> how to do this ) and after making the changes there ...import it back
> to fox 6. but if you have any more suggestions or if you can guide me
> to the right direction .. i will be very thankful

If you want to do it with pure SQL you might have some problems in VFP6.

But if you do it with foxpro code, it should be quite easy.
Open the tables as often as you need (each with a separate alias), add/set some
indexes, set relations between the aliasses, set filters, loop through the
filtered records and do whatever you need.
BTW, there is a kind of implicit primary key at every foxpro table: the RECNO().
As long as you don't PACK, COPY or SORT a table, it is never changed.

Regards
Bernhard Sander