By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,665 Members | 1,900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,665 IT Pros & Developers. It's quick & easy.

rupdating duplicate entries with random numbers

P: n/a
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

Jul 10 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

Roy Harvey
Beacon Falls, CT

On Wed, 9 Jul 2008 22:42:17 -0700 (PDT), php_mysql_beginer911
<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
Jul 10 '08 #2

P: n/a
Roy Harvey (SQL Server MVP) (ro********@snet.net) writes:
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

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
Jul 10 '08 #3

P: n/a
Thanks, Erland, for straightening that out. 8-)

Roy

On Thu, 10 Jul 2008 22:08:33 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.sewrote:
>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

Jul 10 '08 #4

P: n/a
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 "
Jul 11 '08 #5

P: n/a
On Fri, 11 Jul 2008 01:54:31 -0700 (PDT), php_mysql_beginer911
<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
Jul 11 '08 #6

P: n/a
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
Jul 13 '08 #7

P: n/a
php_mysql_beginer911 (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****@sommarskog.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
Jul 13 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.