SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update numeric field + 1 (increment)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

purplecruz
Starting Member

USA
7 Posts

Posted - 09/16/2008 :  13:10:09  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 09/16/2008 :  13:14:17  Show Profile  Reply with Quote
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?
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 09/16/2008 :  13:23:18  Show Profile  Reply with Quote
do you mean auto increment value ?

CREATE TABLE [dbo].[delete](
	[MyID] [int] IDENTITY(1,1))
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/16/2008 :  13:28:03  Show Profile  Reply with Quote
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



Go to Top of Page

purplecruz
Starting Member

USA
7 Posts

Posted - 09/16/2008 :  13:31:30  Show Profile  Reply with Quote
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
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 09/16/2008 :  13:55:24  Show Profile  Reply with Quote
use auto increment ID
quote:
Originally posted by afrika

do you mean auto increment value ?

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


Go to Top of Page

purplecruz
Starting Member

USA
7 Posts

Posted - 09/16/2008 :  14:19:13  Show Profile  Reply with Quote
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
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 09/16/2008 :  14:27:20  Show Profile  Reply with Quote
Paste some sample code
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/16/2008 :  15:56:12  Show Profile  Reply with Quote
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



Go to Top of Page

purplecruz
Starting Member

USA
7 Posts

Posted - 09/16/2008 :  16:54:14  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/17/2008 :  00:13:29  Show Profile  Reply with Quote
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.
Go to Top of Page

carrodch
Starting Member

Costa Rica
1 Posts

Posted - 10/03/2012 :  12:57:21  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000