• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Increment an int field in db

Hi,

How do i increment a int field coluimn in a db in 1 sql command
instead of having to read select and update later.
0
jedistar
Asked:
jedistar
1 Solution
 
o0JoeCool0oCommented:
try this
UPDATE Test1 SET tmp=(tmp + 1) Where Id=1

0
 
jedistarAuthor Commented:
How do i write that in stored procedures?
0
 
o0JoeCool0oCommented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
vinodhsomasekharanCommented:

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
(
TempOrderId,
TempProdName
)
select
orderId,
prodName
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,
Vinu

0
 
RejojohnyCommented:
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
0
 
o0JoeCool0oCommented:
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
0
 
RejojohnyCommented:
>>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 ..

Rejo
0
 
o0JoeCool0oCommented:
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.

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

Rejo
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now