Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Incrementing the value in column in Sql server

Posted on 2014-03-20
3
Medium Priority
?
192 Views
Last Modified: 2014-04-01
I have  the following requirement.

I have the table with following structure

TranType, episodeid, Adviseserialno


I want  to update AdviseSerialno  in such a way that the next number is incremented by 1 from previous number. This sequence number should get reset (I,e should start from 1 )
for each change of Episodeid.

How I can achieve this using SQL . DB is Sql 2008R2

Thanks in advance
0
Comment
Question by:venkataramanaiahsr
3 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 39942093
To start with, you'll know TranType and EpisodeID so let's say that the new values are in  @TranType and @EpisodeID.

Declare an int (I'm assuming AdviseSerialNo is an int) @intAdviseSerialNo and do this:

declare @intAdviseSerialNo int

select @intAdviseSerialNo = max(Adviseserialno) + 1
from TableName 
where EpisodeID = @EpisodeID

if @intAdviseSerialNo is null set @intAdviseSerialNo = 1

insert into TableName (TranType, EpisodeID, AdviseSerialNo) 
   select @TranType, @EpisodeID, @intAdviseSerialNo

Open in new window


Hope this helps

Mike
0
 
LVL 15

Accepted Solution

by:
gplana earned 2000 total points
ID: 39942118
Try something like this (I have not tested it, but you can get the idea):

DECLARE @episId NUMBER;
DECLARE c CURSOR FOR SELECT DISTINCT EpisodeId FROM your_table;
open c;
FETCH NEXT FROM c INTO @episId;
WHILE @@FETCH_STATUS = 0 
BEGIN
    SELECT @nextId = MAX(AdviseSerialNo)+1 FROM your_table WHERE EpisodeId=@episId;
    UPDATE TOP(1) your_table SET AdviseSerialNo=@nextId WHERE EpisodeId=@episId AND AdviseSerialNo IS NULL;
    FETCH NEXT FROM c INTO @episId;
END
close c;

Open in new window


Hope it helps. Regards.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39942505
How do you order the records? Do you have any date column or ID column to get the next record?
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question