rupdating duplicate entries with random numbers

[php_mysql_beginer911]

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

[Roy Harvey (SQL Server MVP)]

What version of SQL Server are you running? It makes a BIG
difference. Or, as your name ( php_mysql_begin er911) 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

Roy Harvey
Beacon Falls, CT

On Wed, 9 Jul 2008 22:42:17 -0700 (PDT), php_mysql_begin er911
<de******@gmail .comwrote:

>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

[Erland Sommarskog]

Roy Harvey (SQL Server MVP) (ro********@sne t.net) writes:

What version of SQL Server are you running? It makes a BIG
difference. Or, as your name ( php_mysql_begin er911) 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_begin er911'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

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Roy Harvey (SQL Server MVP)]

Thanks, Erland, for straightening that out. 8-)

Roy

On Thu, 10 Jul 2008 22:08:33 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.sewrote:

>Nah, there are a couple of problems. First, in php_mysql_begin er911'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

[php_mysql_beginer911]

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 "

[Roy Harvey (SQL Server MVP)]

On Fri, 11 Jul 2008 01:54:31 -0700 (PDT), php_mysql_begin er911
<de******@gmail .comwrote:

>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 "

So? You asked your question in a group devoted to Microsoft SQL
Server. If you aren't using SQL Server you are wasting everyone's
time asking here.

Roy Harvey
Beacon Falls, CT

[php_mysql_beginer911]

Hi Roy..
it's my mistake i thought foxpro uses sql so it would be ok to ask
here
my apolozies
and thanks for the help

[Erland Sommarskog]

php_mysql_begin er911 (de******@gmail .com) writes:

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

Foxpro does indeed use SQL, but there are considerable differences
between SQL dialects in different products, and you cannot always expect
a query that works in one product to work in another. Had you specified
that you wanted a portable query, we might have tried to compose one
for you. Although, I think it would have been difficult in this case,
as there is no portable randomisation construct in SQL as far as I know.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx