Update column with an incrementing value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update column with an incrementing value

Author

Topic

hard_life
Starting Member

4 Posts

Posted - 2010-03-08 : 11:15:10

Hello,

I am trying to 'obfuscate' some phonenumbers in a table from a dump of a live-database. I want to use the data in a testscenario.

The database is fairly simple, and the phonenumber in this particular table is the only 'sensitive' piece of data. The phonenumber is also the primarykey in the table i want to update.

In order to be able to update this field, i have changed the associations between this and related tables temporarily to cascade on update.

Phonenumbers are stored as a bigint.
I want to give every row a phonenumber starting at 10000 and rising.
So in the situation where i have exactly 10000 entries in the table with cellphonenumbers as primary key, i want to update all of them leaving them with cellphonenumbers 10000-20000.

How can i accomplish this?

Cheers!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 11:18:16

[code]UPDATE t
SET t.phonenumber=10000+(RowNo-1)
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY phonenumber) AS RowNo,phonenumber
FROM YourTable)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hard_life
Starting Member

4 Posts

Posted - 2010-03-09 : 03:19:37

That certainly worked!.

Could you attach maybe a few words to what exactly is going on here? I'm pretty clueless.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 03:39:42

quote:
Originally posted by hard_life

That certainly worked!.

Could you attach maybe a few words to what exactly is going on here? I'm pretty clueless.

Get a clue from here

SELECT ROW_NUMBER() OVER (ORDER BY phonenumber) AS RowNo,phonenumber
FROM YourTable

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:32:47

quote:
Originally posted by hard_life

That certainly worked!.

Could you attach maybe a few words to what exactly is going on here? I'm pretty clueless.

just using row number function to generate row numbers on the fly on order of phonenumber field and then using the formula 10000+(RowNo-1) to make it start from 10000 and continue as 10001,10002,...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources