table values from 1001 - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

table values from 1001

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-06-09 : 08:27:59

hi i am having one table with 4 columns where some 1500 records are there.now i craeted one new column with number data type and i want value to be updated for that column starts with 1000 onwards .how to do that.give me some sample code for this.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-09 : 08:35:10

Assuming your table has an ID or something.


update t1
set newColumn=dt.newNumber
from your_table t1
join
(select
 row_number() over (order by ID) + 999 as newNumber,
 ID
 from your_table)dt
on dt.ID = t1.ID

No, you're never too old to Yak'n'Roll if you're too young to die.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-09 : 08:35:23

ALTER TABLE tablename ADD MyCounter int NOT NULL IDENTITY(1000, 1)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 08:36:42

<<
i craeted one new column with number data type
>>

Are you using ORACLE?

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-06-09 : 08:48:38

yes in both

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 08:51:56

In ORACLE, you may need to use either row_number() or a sequence

Madhivanan

Failing to plan is Planning to fail