SQL QUERY PLEASE ANSWER ThIs

Question

1.00/5 (2 votes)

See more:

SQL-Server-2005

i have a table with colmn of serial no. whose indentity specification i have define starting from 101
but every time i use simple query of delete..
i.e. delete from tablename where serialno=@serialno
it deletes that id
i want..that after deleting it shud be assigned to next serial no.....how can i do that..please suggest some query about the fACT.....

HERE SERIAL NO COULMN IS A PRIMARY KEY THAT I HAVE DEFINED/...
sOME BODY PLEASE HELP ME IN THIS

Posted 2-Dec-10 4:14am

Aksh@169

Updated 2-Dec-10 4:15am

v2

Add a Solution

Comments

Sandeep Mewara 2-Dec-10 10:23am

Not clear.

Corporal Agarn 2-Dec-10 11:58am

In the future please try to get a better title. I had no idea as to the question from this title.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2010-12-02T04:40:00

You should not be using serial numbers as keys in your database - but that's a discussion for another time.

What you want to do is not scalable. If you want to reuse a serial number that has already been passed, you'll have to add that serial number to a table that only contains serial numbers to be reused.

Next, you'll have to add a stored procedure that generates and returns serial numbers. This SP would have to check for an available serial number in the table and if one isn't found, would have to generate the next serial number. The problem is that two machines can run this SP at the same time and come up with the same serial number.

In other words, you shouldn't be recycling serial numbers at all.

sandipapatel · Answer 2 · 2010-12-02T05:24:00

Hi,
If possible please change the schema of database. You should not use serial no. as PK. You can have u r own id instead.
Like, In AutoIncrement for an Primary key of a table (type int) will increment id by 1 , and "when u delete that row it will delete that id(ofcourse with row)." and now when u insert new row it will "skip the deleted id and adds new one which is incremented."

e.g.
======== Initially=========
ID
1 .......
2 .......
3 .......
===========================
delete command for row 3.
=========After delete======
ID
1 .......
2 .......
===========================
insert command for new row
=========After insert======
ID
1 .......
2 .......
4 .......

"Checkout that 3 will not happen again."
Some thing like this can be happening with u r db also.:thumbsup:

Dave Kreskowiak · Answer 3 · 2010-12-02T12:56:00

Solution 3

I already told you how to do it.

Posted 2-Dec-10 12:56pm

Dave Kreskowiak

SQL QUERY PLEASE ANSWER ThIs

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0