Using Deleted primary key

Question

0.00/5 (No votes)

See more:

Hello,
I have a table XYZ which contains a column "srno". It has no constraint(s), no Sequences, no trigger, I have assumed that the srno is unique. The table has no relation with any other table.

My client has a requirement that when a row is deleted, The deleted "srno" should be reused.

How can I accomplish this using SQL Query.
Till now I am using

SQL

select max(nvl(Srno,0))+1 from xyz

to Find out the new SRNO which will return the missing or deleted srno for Re-Use!

Prathamesh

Posted 25-Jul-13 18:30pm

prathameshpitale

Add a Solution

2 solutions

Solution 2

there are two ways, you can add new records to missing srno which is deleted, like if you have 1 to 10 records having srno in sequence 1,2,3 etc. and you are deleting the 3rd srno, and now you are inserting new record then first of all you need to find out the missing srno and use that srno while you are inserting new entry.

or

if you want something like this, 1,2,3...10 and you are deleting 3rd srno then rest of the entries are auto adjust with proper sequence like 1,2,3,4,5,6,7,8,9, so for this you need to fire update query every time to update proper srno to rest of the records whenever you are deleting any record.

Posted 25-Jul-13 19:00pm

Tejas Vaishnav

Updated 25-Jul-13 19:02pm

v2

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)

Ron Beyer · Accepted Answer · 2013-07-25T18:50:00

Solution 1

I would insert the deleted SRNO into another table and if that table has rows use an SRNO from that table (delete it when done). If it has no rows you can use the method you outlined above.

Posted 25-Jul-13 18:50pm

Ron Beyer