DELETE查询后更新自动编号字段 [英] Update Autonumber field after DELETE query
问题描述
介绍及相关信息：
我有 MS ACCESS 2007 我使用 ADO 和 C ++ 编辑的数据库。
问题：
我的问题是主键也代表记录的序号，删除后，应该正确更新。主键是自动编号类型。
这是我所说的一个例子：
INTRODUCTION AND RELEVANT INFORMATION:
I have MS ACCESS 2007 database that I edit using ADO and C++.
PROBLEM:
My problem is that primary key also represents an ordinal number of the record, and after deletion, it should be properly updated. Primary key is of autonumber type.
Here is an example of what I am talking about:
| #PK | Other data ... |
| 1 | ... |
| 2 | ... |
| 3 | ... |
| 4 | ... |
| 5 | ... |
现在如果删除第3条记录，我会遇到以下问题：
Now if I delete the 3rd record I get the following problem:
| #PK | Other data ... |
| 1 | ... |
| 2 | ... |
| 4 | ... |
| 5 | ... |
但是我应该得到以下结果：
but I should get the following result:
| #PK | Other data ... |
| 1 | ... |
| 2 | ... |
| 3 | ... | // edited to reflect the change ( previous value was 4 )
| 4 | ... | // edited to reflect the change ( previous value was 5 )
如果我删除最后一条记录然后插入新记录，我会得到这样的结果：
If I delete last record and then insert new one I get this result:
| #PK | Other data ... |
| 1 | ... |
| 2 | ... |
| 3 | ... |
| 4 | ... |
| 6 | ... | // this should be 5
< b> 问题：
有没有办法让我以编程方式更新自动编号字段执行 DELETE 查询后？
我很清楚这一点是不好的做法，所以我宁愿添加应该是序数的新字段，这样我的表格可能如下所示：
QUESTIONS:
Is there a way for me to programmatically update the autonumber field after I perform the DELETE query ?
I am well aware this is bad practice, so I would prefer adding new field that should be ordinal number so my table can look like this:
| #PK | Ordinal | Other data ... |
| 1 | 1 | ... |
| 2 | 2 | ... |
| 4 | 3 | ... |
| 5 | 4 | ... |
但我希望它能自动更新。如果无法做到这一点，我希望在执行删除后用 SQL查询更新字段。
谢谢。
祝你好运。
but I would prefer it to update itself automatically. If this is not possible, I would prefer to update the field with SQL query after I perform the deletion.
Thank you.
Best regards.
推荐答案
如果 您只需要报告/显示的序数字段，那么带有计算字段的查询是否可能成为解决方案？
我没有'得到了Access的副本，但这种方法的T-SQL如下所示。在我创建视图的位置，您将创建Access查询并使用该查询而不是表格作为报告/显示的基础。幸运的是，将T-SQL视图转换为Access查询应该是不可能的。
If you only need the ordinal field for reports/display would a query with a calculated field be a possible solution?
I haven't got a copy of Access to hand, but the T-SQL for this approach would be as shown below. Where I've created a view you'd create your Access query and use that instead of the table as the basis for reports/display. With luck the conversion of the T-SQL view to Access query shouldn't be impossible.
create table a
(
-- T-SQL equivalent of autonumber
pk int identity (1,1),
data varchar(20)
)
create view ordinalA as
select
-- Our calculated index field
count(prev.pk) + 1 as [index],
main.pk,
main.data
from a as main
left join a as prev on prev.pk < main.pk
group by main.pk, main.data
go
一些可以使用的数据。
Some data to play around with.
insert into a (data) values ('alpha')
insert into a (data) values ('beta')
insert into a (data) values ('gamma')
insert into a (data) values ('delta')
insert into a (data) values ('epsilon')
insert into a (data) values ('zeta')
表和视图/查询是否一致？
Are the table and view/query in agreement?
select * from a
select * from ordinala
pk data
----------- --------------------
1 alpha
2 beta
3 gamma
4 delta
5 epsilon
6 zeta
index pk data
----------- ----------- --------------------
1 1 alpha
2 2 beta
4 4 delta
5 5 epsilon
3 3 gamma
6 6 zeta
现在扔掉一行。
Now throw away a row.
delete a where pk = 3
select * from a
pk data
----------- --------------------
1 alpha
2 beta
4 delta
5 epsilon
6 zeta
幸运的是，视图/查询应该被正确编入索引。
And with luck the view/query should be correctly indexed.
select * from ordinala
index pk data
----------- ----------- --------------------
1 1 alpha
2 2 beta
3 4 delta
4 5 epsilon
5 6 zeta
我决定在我的表格中添加一个新字段，该字段将保存记录的序号。
如果我们假设该字段名为 OrdinalNumber ，则以下解决方案适用于我：
I have decided to add a new field in my table that will hold the ordinal number of the record.
If we assume the field is named OrdinalNumber then the following solution worked for me:
// when inserting record, I just had to add COUNT( PK ) + 1
INSERT INTO MyTable ( OrdinalNumber , ... ) SELECT COUNT( PK ) + 1 , ...
from MyTable ;
// when deleting, I had to perform following two queries :
DELETE from MyTable where PK = ? ;
// decrement all the successors ordinal number by one
UPDATE MyTable set OrdinalNumber = ( OrdinalNumber - 1 ) where ( PK > ? );
一切似乎都运作良好。我希望有一个更简单的方法......
Everything seem to work well. I wish there was an easier way though...
这篇关于DELETE查询后更新自动编号字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
