Update numeric field + 1 (increment)

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 numeric field + 1 (increment)

Author

Topic

purplecruz
Starting Member

7 Posts

Posted - 2008-09-16 : 13:10:09

I have what I think should be a simple question:

I would like to update a number field in a table by incrementing the numbers by 1. Kind of like an auto number.

I have been trying to use an update statement with count + 1 but cannot get it to work. I would like it to keep going until it gets to the last entry so will need some type of loop. If possible, I would also like the numbering to start at the largest number in the table.

Any help would be greatly appreciated!

Stacy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 13:14:17

so you want to update all current numeric values with new ones starting from current max(value) + 1? Whats the purpose of such an update? or are you asking about auto numbering numeric field upon insertion? In which why is the field declared numeric and also why havent you declared it as identity column?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-16 : 13:23:18

do you mean auto increment value ?

CREATE TABLE [dbo].[delete](
	[MyID] [int] IDENTITY(1,1))

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-16 : 13:28:03

No that want an non-identity, identity column

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

purplecruz
Starting Member

7 Posts

Posted - 2008-09-16 : 13:31:30

I baically would like an autonumber field that I calculate. I have populated the field with a place holder (1000001) and I would like to add 1 incrementally down (1000001, 1000002, etc). I haven't worked with SQL in a while so I may be going about this wrong. I hope this makes my question more clear.

Stacy

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-16 : 13:55:24

use auto increment ID

quote:
Originally posted by afrika

do you mean auto increment value ?

CREATE TABLE [dbo].[delete](
[MyID] [int] IDENTITY(100000,1))

purplecruz
Starting Member

7 Posts

Posted - 2008-09-16 : 14:19:13

I was hoping to find a way to update the field I already have in my table. I was thinking I could use some sort of loop with an update statement. Is there a way to do this?

Stacy

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-16 : 14:27:20

Paste some sample code

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-16 : 15:56:12

Can you read my link first?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

purplecruz
Starting Member

7 Posts

Posted - 2008-09-16 : 16:54:14

Thanks for the sample scripts. I did try and execute one that looked appropriate for what I was trying to do. Even though I used the column name I was trying to update, it tried to fill in the primary key field which I do not want to change.

I found a solution though for anyone else who may want to do this:

declare <name of cursor> cursor for
select <id field> from <table name>

open <name of cursor>
fetch from <name of cursor>

declare @counter int
set @counter = <1 or starting number>

while @@fetch_status = 0
begin
update <table name>
set <id field> = @counter
set @counter = @counter + 1
fetch next from <name of cursor>
end

Stacy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 00:13:29

quote:
Originally posted by purplecruz

Thanks for the sample scripts. I did try and execute one that looked appropriate for what I was trying to do. Even though I used the column name I was trying to update, it tried to fill in the primary key field which I do not want to change.

I found a solution though for anyone else who may want to do this:

declare <name of cursor> cursor for
select <id field> from <table name>

open <name of cursor>
fetch from <name of cursor>

declare @counter int
set @counter = <1 or starting number>

while @@fetch_status = 0
begin
update <table name>
set <id field> = @counter
set @counter = @counter + 1
fetch next from <name of cursor>
end

Stacy

still cant understand purpose for this.You could have simply added an identity column as suggested by many of us to get this autoincremented value in your table.

carrodch
Starting Member

1 Post

Posted - 2012-10-03 : 12:57:21

quote:
Originally posted by visakh16

quote:
Originally posted by purplecruz

Thanks for the sample scripts. I did try and execute one that looked appropriate for what I was trying to do. Even though I used the column name I was trying to update, it tried to fill in the primary key field which I do not want to change.

I found a solution though for anyone else who may want to do this:

declare <name of cursor> cursor for
select <id field> from <table name>

open <name of cursor>
fetch from <name of cursor>

declare @counter int
set @counter = <1 or starting number>

while @@fetch_status = 0
begin
update <table name>
set <id field> = @counter
set @counter = @counter + 1
fetch next from <name of cursor>
end

Stacy

still cant understand purpose for this.You could have simply added an identity column as suggested by many of us to get this autoincremented value in your table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources