How to rearrange Record after Deletion

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to rearrange Record after Deletion

Author

Topic

umapathy
Starting Member

24 Posts

Posted - 2007-08-08 : 02:54:56

Hi,

i have a table name emp.
the structure of the table is following

s.no int
Emp_code int
Emp_name varchar(50)

the column s.no contains number from 1 to n.it never allow nulls.but auto increments.if i delete any records from the table it has to order itself.
Example, if i delete 3rd record ie s.no 3. it has to rearrane it self

Before Delete record 3

s.no Emp_code Emp_name
1 222 xxx
2 223 yyy
3 224 zzz
4 225 aaa

After Deletion of Record 3

s.no Emp_code Emp_name
1 222 xxx
2 223 yyy
3 225 aaa

In what way should i acheive this?

Regards

Umapathy

alanlambert
Starting Member

26 Posts

Posted - 2007-08-08 : 03:31:11

To take your example of deleting item 3, after the delete you would want to do the following:

UPDATE emp
SET [s.no] = [s.no]-1
WHERE [s.no] > 3

You should run both the original delete query and the update in a single transaction or, probably better, run the update in a trigger.

The above, as per your example, assumes that you will only be deleting a single record at a time.

Alan

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-08 : 03:55:41

If it has auto increments set would it not disallow you to modify that field?

Why do you want to change this id anyway - what if you had other tables referencing this field, it will cause all kinds of trouble!

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-08 : 04:22:17

Where do you want to show data so that serial no is important?
If you use front end application, do numbering there. You dont need to worry about renumbering it

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-08 : 04:25:29

Hey Madhi, glad to see that you still punt the front end applications for all the right reasons. [D]
Nothing much has changed since I last visited.

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources