Increment an int field in db


How do i increment a int field coluimn in a db in 1 sql command
instead of having to read select and update later.
Who is Participating?
The same way although you might use a parameter to specify the ID (which row) to update

CREATE PROC Noodles (@RID int) as
UPDATe Test1 SET tmp=(tmp + 1) Where ID=@RID
try this
UPDATE Test1 SET tmp=(tmp + 1) Where Id=1

jedistarAuthor Commented:
How do i write that in stored procedures?
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.


I think u need to increment a integer field..

My suggestion is to create a temp table with an identity column in it..

Try this.. hope this might work..

create table MyActualTable
  orderId int identity(1,1) primary key,
  orderSlno int,
  prodName varchar(200)

insert into MyActualTable values (0,'Milk')
insert into MyActualTable values (0,'Water')
insert into MyActualTable values (0,'Tea')

select * from MyActualTable

orderId     orderSlno   prodName                                                                                                                                                                                                
----------- ----------- --------
1           0           Milk
2           0           Water
3           0           Tea

--now i hope u need to update the orderSlno field to increment by step of 1

declare @TempMyTable table
  TempOrderId int,
  TempOrderSlno int identity(1,1),
  TempProdName varchar(200)

insert into @TempMyTable
from MyActualTable

-- now in the @TempMyTable , u will be getting the TempOrderSlno in a order incremented by 1

-- now just update the orderSlno in  MyActualTable with the values of TempOrderSlno from @TempMyTable

update MyActualTable
set orderSlno = TempOrderSlno
from MyActualTable inner join @TempMyTable on (orderId = TempOrderId)

select * from MyActualTable

orderId     orderSlno   prodName                                                                                                                                                                                                
----------- ----------- ----------
1           1           Milk
2           2           Water
3           3           Tea

-- Hope this will be your desired result..

With regards,

just make that field as a "identity" column .. read books online on how to do that using ur SQL enterprise manager .. open table in design mode .. select the field .. set the field as "indetity column" by selecting the approriate properties for that field .. save the table ..

after u insert the the other fields in the table .. this identity column would automatically get increamented with the new incrememented value .. use @@identity or scope_identity() to get the new "ID"

so ur SP would have

insert into tablename(all other fields) values (all other values)
select @@identity -- return the newly created id
to me it sounds liek hes trying to use code to increment the field on command... in which case an identity field really isnt going to help
>>to me it sounds liek hes trying to use code to increment the field on command
on command? do u mean even during update? or just insert? if insert the identity column would have helped and is the right way of doing it .. anyway jedistar does not seem to think so .. he has accepted a comment .. so I think i will leave it there and hope that others who refer to this thread would have an idea on how to use a identity column in similar situations ..

If you need to incrememnt a field by one each time u insert a record then you use an identity column, not if you have a row and you want to incrememnt the value in that row by one. and from what I gathered from his question he had an existing row and was trying to update it without first selecting it to see what the value in that field was.

which I must assume was a correct assumption as he accepted my answer.

I didnt mean to knock down your answer, just was trying to point out to him that an identity field wont help if your trying to incrememtn an existing row.

ok .. cool ... should read the question more carefully :-) ..

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.