Help Needed At Deleting Records

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 2000 Forums

SQL Server Development (2000)

Help Needed At Deleting Records

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2007-04-14 : 00:40:47

Hi,
Here I Have Table named tbl_employee which contains some information regarding the employees.

the table structure is:

id int primary key @identity,
Name varchar(100) not null

The Data in the table is:
id Name
1 xxx
2 yyy
3 zzz
4 xyz
5 xxz

Here i have to delete the records containing id 2

so i have written command as:
delete from tbl_employee where id = 2
then the remaining data is :

id Name
1 xxx
3 zzz
4 xyz
5 xxz
but, My task is : after deleting the record having id 2, the rest of the data should be like the way shown in below:

id Name
1 xxx
2 zzz
3 xyz
4 xxz

i.e the id's should be adjusted after deleting the previous record.

so please suggest me a way to achieve it.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-14 : 00:48:01

In order to do that, you need to copy the records into a separate table or even temp table, do the delete, reseed the IDENTITY value in the table and re-insert the data back.

Having said this, what is the need to re-assign the numbers and move them up? Is this ID FK'ed to other tables? If so you need to update all those tables which is again a pain.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rammohan
Posting Yak Master

212 Posts

Posted - 2007-04-14 : 00:57:34

Hi Dinkar,
Thank you for posting the reply. can u explain in details with some queries.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-14 : 03:00:31

Identity value are not re-used after a record is deleted.

quote:
the id's should be adjusted after deleting the previous record.

Are you doing to do this every time you delete a record ?
Why is it important that the id value is continuous ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources