SQL查询请回答 [英] SQL QUERY PLEASE ANSWER ThIs

问题描述

我有一个表，其序列号为colmn.我已从101开始定义其身份规范
但是每次我使用删除的简单查询..
即从表名称中删除，其中serialno = @ serialno
它会删除该ID
我想要..删除后将其分配给下一个序列号...我该怎么做..请提出一些有关事实的查询.....

在这里，序列号"不是我定义的主键/...
某些人请在此

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

推荐答案

中帮助我.您不应该将序列号用作数据库中的键-但这是另一次讨论. >
您想要做的是不可扩展的.如果要重用已经传递的序列号，则必须将该序列号添加到仅包含要重用的序列号的表中.

接下来，您将必须添加一个存储过程，该存储过程生成并返回序列号.该SP必须检查表中的可用序列号，如果找不到，则必须生成下一个序列号.问题是两台计算机可以同时运行此SP并提供相同的序列号.

换句话说，您根本不应该回收序列号.

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.

如果可能，请更改数据库的架构.您不应该使用序列号.作为PK.您可以改用自己的ID .
就像在表的主键的AutoIncrement中(类型int)将id递增1，并且当您删除该行时，它将删除该id(当然带有行)".现在，当您插入新行时，它将跳过已删除的ID并添加递增的新ID."

例如
========最初=========
ID
1 .......
2 .......
3 .......
=========================
删除第3行的命令.
=========删除后======
ID
1 .......
2 .......
=========================
为新行插入命令
=========插入后======
ID
1 .......
2 .......
4 .......


结帐3不会再次发生."
这样的事情也可能在您的数据库中发生.:thumbsup:

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:

我已经告诉过您如何做.

I already told you how to do it.

这篇关于SQL查询请回答的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！