Renumbering... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Renumbering...

Author

Topic

fabs
Starting Member

2 Posts

Posted - 2007-08-07 : 22:22:21

Hi everyone.. kindly help me out.. im a newbie by the way.. ihave this fields in my table... uid, linenum, name... my problem is everytime i wnt to delete a record i want the "line: field to be renumbered.. for exmple...
record 1:
uid=1, line=1, name=Bob
record 2:
uid=2, line=2, name=Greg
record 3:
uid=3, line=3, name=Don...

now, i deleted record 2... whatt should happen is line must be renumbered..
record 1:
uid=1, line=1, name=Bob
record 2:
uid=3, line=2, name=Don

im thingking that i should create a trigger... but how will be my approach in SQL script.. help.. pls...

FaBs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-07 : 22:28:57

You should not do this in trigger.

Can you do the numbering in your front end application when you display the data ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-07 : 22:31:04

Actually, you should not have the numbering stored in the table in the first place. Sequential numbering can be easily done in the front end.

KH
[spoiler]Time is always against us[/spoiler]

fabs
Starting Member

2 Posts

Posted - 2007-08-07 : 23:38:36

im using VB.net 2005 as my front end..

but it is required that the numbering is in the database...
what's the best approach for this? considering that the field line must be in the database...

FaBs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-07 : 23:40:51

quote:
considering that the field line must be in the database

Any particular reason why do you need to have it there ?

KH
[spoiler]Time is always against us[/spoiler]

sbalaji
Starting Member

48 Posts

Posted - 2007-08-08 : 06:23:20

can update the table column after delete

declare @count int
select @count = 0

update <table name>
set line = @count,
@count = @count + 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-08 : 07:29:50

quote:
Originally posted by sbalaji

can update the table column after delete

declare @count int
select @count = 0

update <table name>
set line = @count,
@count = @count + 1

that would not be very efficient, assuming you have 1 million records, and you deleted uid = 2

KH
[spoiler]Time is always against us[/spoiler]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-08-08 : 07:29:56

"but it is required that the numbering is in the database"
When you say REQUIRED, i think you mean, "That is the way it is and I don't want to fix it".

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-08 : 08:00:53

quote:
"That is the way it is and I don't want to fix it"

If that's the case, all i can say is Good Luck

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-08 : 11:45:41

What is the purpose of the Line number?

It looks like the UID is a sequential number already. Do you need to re-arange the order in which the Names need to be displayed? I'm trying to understand why it needs to be in the database. You did not provide any information as to what the purpose is and why it even needs to exist at all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources