Solved

Incrementing the value in column in Sql server

Posted on 2014-03-20
3
182 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 500 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 40

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
My Query is not giving correct result. Please help 5 39
kill process lock Sql server 9 45
Challenging SQL Update 5 41
MS SQL 2008 Divide by zero error encountered. Error 3 33
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now