How to obtain null values in place of deleted rows in a sequence without affecting other values

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

create table missing_data(sno varchar(3)primary key,name varchar(15),designation char(10))

insert into missing_data values(1,'riz','SE'),(2,'sandy','SE'),(3,'sharein','SE'),(4,'chithu','SE'),(5,'flora','SE'),(6,'gayu','SD'),(7,'maha','SD'),(8,'moni','SD'),(9,'maddy','AE'),(10,'naz','AE')

select * from missing_data order by sno

delete from missing_data where name='gayu'

What I have tried:

I had deleted name='gayu' in the above table and i want sno = 6 to appear on the table after delete with null values in the row without affecting sequence.. is that possible in sql table???

Posted 22-Feb-16 18:35pm

alia007

Updated 23-Feb-16 2:09am

Add a Solution

Comments

dan!sh 23-Feb-16 0:57am

Don't delete it. Rather when the application makes a delete call, just set the fields to null.

alia007 23-Feb-16 1:02am

can u explain with an example??

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 23-Feb-16 1:20am

He is saying not to delete the whole row rather just set field values to null.

alia007 23-Feb-16 1:24am

thanks !!! I got it

dan!sh 23-Feb-16 1:29am

Check this link. If all you want is to find missing sequence. If you want to implement audit trail kind of functionality, that's a totally different story.

alia007 23-Feb-16 2:11am

can a missing value in a sequence be incremented??? I mean imagine that there are 10 ID's and the ID 7 is missing in sequence whether is it possible to increment +1 only where the ID 7
is missing...

dan!sh 23-Feb-16 2:15am

Not clear. Try to rephrase.

alia007 23-Feb-16 2:20am

S.No Date CollTime Branch Company Rec-ser ReceiptNo Receipt

1 2016-02-19 11:22:18am SP CFMS SP18 SP18004315 4315

2 2016-02-19 11:39:38am SP CFMS SP18 SP18004316 4316

3 2016-02-19 11:41:21am SP CFMS SP18 SP18004317 4317

4 2016-02-19 12:51:12pm SP CFMS SP18 SP18004319 4319

alia007 23-Feb-16 2:21am

the last column is in sequence and 4318 is missing ...can it be incremented manually when missing in sequence???

1 solution

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)

ZurdoDev · Accepted Answer · 2016-02-23T02:09:00

Solution 1

You don't ever want to reuse Ids. If you delete a row then that ID is gone and it is very bad practice to use it again. Instead, if you don't really want it deleted, add a field named deleted_time and store the time it is deleted. Then filter those out when retrieving your data.

Posted 23-Feb-16 2:09am

ZurdoDev

Comments

alia007 24-Feb-16 0:04am

I got your point Ryan.. Thanks a lot for helping me out.