使用临时变量自动更新表字段 [英] update table field automatic using temp variable

问题描述

大家好，

我很困惑怎么会不知道请帮助我： -



我用三张桌子。 name是1）ExpectedVisitorTable 2）GatePassTable

现在我首先在Expected Visitor c＃Form中插入记录并在ExpectedTable中保存记录。



然后我在GatePass表格上获得预期访客记录。然后我在GatePass表中保存记录然后自动更新预期访问者表的fileld名称是expectedentry 0（零）（注意 - expectedentry默认值是0（零）但我想更新为1（一））



现在如何更新自动expectedentry字段。哪个查询用于更新???

hello everyone,
i am confused how can i do not know pls help me:-

i use three table. name is 1) ExpectedVisitorTable 2) GatePassTable
now first I insert record in Expected Visitor c# Form and save record in ExpectedTable.

then i get Expected Visitor's record show on GatePass Form. and then i save record in GatePass Table and then automatic update expected visitor table's fileld name is expectedentry 0(zero) (note- expectedentry default value is 0(zero) but i want to update to 1(one))

now how can i update automatic expectedentry field. which query i use to update???

推荐答案

如果我理解您的要求，那么您将在ExpectedVisitorTable中保持唯一访问者记录并且每次都有一个门传递给访问者，然后在GatePassTable中重复多次ExpectedVisitorTable的访客ID。根据访客ID条目的数量，ExpectedVisitorTable中的expectedentry列应自动增加。例如，访问者A添加到预期表然后再次添加到GatePass表，在此期间expectedentry应自动增加到1.当下次访问者A访问时，将不会在ExpectedVisitor表中创建任何新条目，但将创建一个条目进入GatePass表，期间expectEntry列应自动增加到2，依此类推。



如果我的理解是错误的，那么请发布两个表的完整表模式和更多解释你的要求（你也提到你使用3个表但只提到两个）。



根据我上面解释的理解，我们可以使用以下解决问题的方法：

方法＃1：您可以将expectedEntry列设为Computed列，因为它是我们需要进行的跨表计算。所以我们需要创建一个函数，它将一个参数作为visitorId。示例函数：

If i am understanding your requirement right then you are going to maintain unique visitors record in the ExpectedVisitorTable and every times a gate pass is issued to the visitor then that many times visitor ID of ExpectedVisitorTable will be repeated in the GatePassTable. And based on the number of visitor ID entries the expectedentry column in ExpectedVisitorTable should increment automatically. For example, Visitor A added to Expected table then again added to GatePass table during which expectedentry should increment automatically to 1. When next time again Visitor A visits then there will not be any new entry made into the ExpectedVisitor table but an entry will be made into the GatePass table during which the expectedEntry column should auto increment to 2 and so on.

If my understanding is wrong then please post full table schema of both the tables and some more explanation to your requirement ( Also you have mentioned that you are using 3 tables but mentioned only two).

Based on my understanding explained above, We can use following approaches to solve the problem:
Approach#1: You can make the expectedEntry column as Computed column, since it is cross table computation we need to do. So we need to create one function which takes one parameter as visitorId. Sample function :

CREATE FUNCTION dbo.IncrementExpectedEntry(INT @VisitorId)
RETURNS INT
AS 
   SELECT count(VisitorId) 
   FROM GatePassTable 
   WHERE VisitorId = @VisitorId

然后使用以下alter命令添加计算列：

Then add the computed column with following alter command:

ALTER TABLE dbo.ExpectedVisitorTable
   ADD expectedEntry AS dbo.IncrementExpectedEntry(VisitorId) PERSISTED

< br $>


注意：计算列可以保存为虚拟或物理，如果您不希望物理存储计算列值只是希望将值作为普通列返回，则可以从上面的列中删除PERSISTED关键字查询。



方法＃2：无需创建任何列，但无论您要在表单中显示expectedEntry值，在查询中，在获取记录时，您可以使用一个子查询来获取GatePassTable中访问者ID的计数。示例查询：

Note: computed column can be kept as virtual or physical, if you don't want the computed column value to be stored physically just want the value to be returned as a normal column you can remove the PERSISTED keyword from the above alter query.

Approach#2: No need to create any column but wherever you are going to display the expectedEntry value in the form, then in the query while fetching the records you can have one sub-query to get the count of visitor ID from GatePassTable. Sample query:

select *,(SELECT count(VisitorId) 
   FROM GatePassTable 
   WHERE GatePassTable.VisitorId = ExpectedVisitorTable.VisitorId) as expectedEntry
from ExpectedVisitorTable

方法＃3：无论何时向GatePassTable插入新访问者，然后使用更新查询更新expectedEntry，如：

Approach#3: whenever you are inserting new visitor to GatePassTable then using update query update the expectedEntry like :

update ExpectedVisitorTable set expectedEntry= (SELECT count(VisitorId) 
   FROM GatePassTable 
   WHERE GatePassTable.VisitorId = ExpectedVisitorTable.VisitorId) 

有我们可以通过创建视图等来实现这一目标的更多方法。在所有解释的方法中，我将推荐方法＃1。

There are many more approaches through which we can accomplish this like by creating a view etc. Among all the explained approaches i will recommend Approach#1.

这篇关于使用临时变量自动更新表字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！