update rows with incremental numbers

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 2000 Forums

Transact-SQL (2000)

update rows with incremental numbers

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-02-18 : 05:36:07

hello,
I have a table with an id field, how can I update the rows with an increment of 1 starting from 1000 ?

eg, so the first row id = 1000
2nd = 1001
3rd = 1002
and so on

at present the all ids are null

thank you.
Jamie

safigi
Starting Member

15 Posts

Posted - 2004-02-18 : 06:48:20

Hello, you can make like this:

alter table yourtablename add id identity(1000,1)

before this you have to run this:

alter table yourtablename drop column id

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-18 : 07:02:23

This might work too (Assuming you can order your data by some other columns in the table (uniqely))
CREATE TABLE idIncrement(MyID INT, Data VARCHAR(100))

INSERT INTO idincrement VALUES(NULL, 'Data 01')
INSERT INTO idincrement VALUES(NULL, 'Data 02')
INSERT INTO idincrement VALUES(NULL, 'Data 03')
INSERT INTO idincrement VALUES(NULL, 'Data 04')
INSERT INTO idincrement VALUES(NULL, 'Data 05')
INSERT INTO idincrement VALUES(NULL, 'Data 06')
INSERT INTO idincrement VALUES(NULL, 'Data 07')
INSERT INTO idincrement VALUES(NULL, 'Data 08')
INSERT INTO idincrement VALUES(NULL, 'Data 09')
INSERT INTO idincrement VALUES(NULL, 'Data 10')
INSERT INTO idincrement VALUES(NULL, 'Data 11')
INSERT INTO idincrement VALUES(NULL, 'Data 12')
INSERT INTO idincrement VALUES(NULL, 'Data 13')
INSERT INTO idincrement VALUES(NULL, 'Data 14')
INSERT INTO idincrement VALUES(NULL, 'Data 15')
INSERT INTO idincrement VALUES(NULL, 'Data 16')
INSERT INTO idincrement VALUES(NULL, 'Data 17')
INSERT INTO idincrement VALUES(NULL, 'Data 18')
INSERT INTO idincrement VALUES(NULL, 'Data 19')
INSERT INTO idincrement VALUES(NULL, 'Data 20')

--*******************************************

UPDATE a
SET myid = 1000 + (SELECT COUNT(*) FROM idincrement b
WHERE b.Data <= a.Data)
FROM idincrement a

--*********************************************

But Safigi's method is better.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-02-18 : 08:36:16

excellent safigi, this has worked.
thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources