rupdating duplicate entries with random numbers

>Hi ..
>i am trying to update a table 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 | 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

What version of SQL Server are you running? It makes a BIG
difference. Or, as your name ( php_mysql_beginer911) suggests are you
using MySQL but posting questions to a Microsoft SQL Server group?
>
Here is an answer that will only work in SQL Server, but will work in
any version.
>
UPDATE TheTable
SET age = RAND() * 10000
FROM (SELECT name, age, count(*) as dups, min(id) as keepme
FROM TheTable
GROUP BY name, age
HAVING count(*) 1) as X
WHERE TheTable.name = X.name
AND TheTable.age = X.age
AND TheTable.id <X.keepme

>Nah, there are a couple of problems. First, in php_mysql_beginer911's
>sample data, the name was unique, so that condition should be removed.
>Next, rand() is evaluated once, so all rows get the same new age. This
>can be addressed with instead using checksum(newid()). We still need
>one more thing trough, a loop that runs the update until @@rowcount is
>0. That is, since new numbers are random, we may get new duplicates. So
>
>WHILE 1 = 1
>BEGIN
UPDATE TheTable
SET age = checksum(newid())
FROM (SELECT age, count(*) as dups, min(id) as keepme
FROM TheTable
GROUP BY age
HAVING count(*) 1) as X
WHERE TheTable.age = X.age
AND TheTable.id <X.keepme
IF @@rowcont = 0 BREAK
>END
>
>

>Hi Roy and Erland ..
>thanks for the replying so quick..
>i tried running that query in foxpro 6.0 but it givers error
>" unrecognized command verb "

it's my mistake i thought foxpro uses sql so it would be ok to ask
here