asked on

T-SQL : Need help to generate sequence Numbers

Hi Experts,

I have a table as below

Tab1
ClassID RowID SequenceNo
1 1 0
1 2 1
1 3 2
2 1 0
2 4 1

when i try to delete a row from the table based on the ClassID and RowID
EX::
Delete from Tab1 where ClassID = 1 AND ROW ID = 2

Now i need to update the Sequence No's as well like below i.e the sequence no's should be regenerated for that particular class ID. Below row with class id 1 and rowid 3 has sequence no changed to 1 from 2 .

Tab1
ClassID RowID SequenceNo
1 1 0

1 3 1
2 1 0
2 4 1

How can i Achieve this . Any Suggestions.

Thanks

Saurabh Bhadauria

try this ...

Delete from Tab1 where ClassID = 1 AND ROW ID = 2;

update Tab1 set sequenceNo = ROW_NUMBER() OVER ( partition by ClassID ORDER BY Rowid)

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

B_Pati

ASKER

Hi ,

This link helped me get the Answer.

http://stackoverflow.com/questions/7593513/efficient-way-to-update-column-with-arithmetic-sequence-of-numbers-after-delete

B_Pati

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for B_Pati's comment #a38413893

for the following reason:

Using the above link  and with slight modification  helped me in finding the solution.

Qlemo

And why didn't you just try out our suggestions? In particular my SQL is most simple to use - and it fits totally to your request.