在检查SQL Server表中的某些值后，如何在特定列中插入0和1 [英] how do I Insert 0's and 1's in a particular column after checking some values in SQL Server table

问题描述

嗨亲爱的所有人....



有谁能告诉我如何在插入sql server表时在列中插入0和1？





我有一张桌子如下所示

Hi Dear All....

could anyone please tell me how to insert 0's and 1's in a column at the time of insertion in sql server table?


I have a table as shown below

ItmId           ItmName     ItmType     ItemPrice          MinPrice1          MinPrice2

234              abc           a          4.5                NULL              NULL          
245              acc           a          2.8                NULL              NULL
246              bff           b          80.5               NULL              NULL
247              bgf           b          40.3               NULL              NULL
248              bgg           b          55.12              NULL              NULL
249              bth           b          30.9               NULL              NULL
250              bjy           b          78.7               NULL              NULL
251              biu           b          76.8               NULL              NULL
252              bjk           b          69.3               NULL              NULL
253              cfff          c          3.5                NULL              NULL
254              cgf           c          2.8                NULL              NULL
255              cht           c          2.4                NULL              NULL
256              clk           c          4.2                NULL              NULL
257              clo           c          4.6                NULL              NULL
258              ckk           c          4.7                NULL              NULL

这里ItmId是primarykey，类型为int。

MinPrice1和MinPrice2的类型为bit。

上表是在插入过程之后..但是在这里，我希望MinPrice1和MinPrice2不为空..我想更新这些列以查找根据ItmType的最低费率。

这里前两个最小值在MinPrice1中的值为1，第二个最小值在MinPrice2中的值为1.我想得到项目的前4个最小值根据ItmType如下所示：

Here ItmId is primarykey and is of type int.
MinPrice1 and MinPrice2 are of type bit.
The above table is after insertion process.. but here I wants MinPrice1 and MinPrice2 as not null.. I would like to update these columns to findout the minimum rates according to ItmType.
Here first two minimum value will have value 1 in MinPrice1 and second two minimum value will have value 1 in MinPrice2.. I would like to get first 4 minimum values of items according to ItmType as shown below:

ItmId           ItmName     ItmType     ItemPrice          MinPrice1          MinPrice2

234              abc           a          4.5                1                  0      
245              acc           a          2.8                1                  0
246              bff           b          80.5               0                  0
247              bgf           b          40.3               1                  0
248              bgg           b          55.12              0                  1
249              bth           b          30.9               1                  0
250              bjy           b          78.7               0                  0
251              biu           b          76.8               0                  0
252              bjk           b          69.3               0                  1
253              cfff          c          3.5                0                  1
254              cgf           c          2.8                1                  0
255              cht           c          2.4                1                  0
256              clk           c          4.2                0                  1
257              clo           c          4.6                0                  0
258              ckk           c          4.7                0                  0

我该怎么办这个操作在插入过程的同时通过检查表中已经存在的最小值

..



谢谢... 。

How can I do this operation at the same time of insertion process by checking the minimum values
already present in the table..

Thank You....

推荐答案

使用后插入/更新/删除触发器。 />


我认为插入，更新或删除后其他行可能会受到影响，因此触发器是最好的方法。触发器确保如果有多个动作查询源（直接表编辑，多个网页/应用程序），代码总是会触发。



最后一个通用最佳实践规则不是存储派生数据，而是动态得出最小值。像大多数规则一样，可能有例外，但我建议您在妥协之前考虑最佳做法。至少一个触发器将派生数据与原始数据联系起来，并应避免数据不一致。



您可以在触发器中使用任何T-SQL，包括调用存储过程，但避免循环操作/级联/嵌套触发器。以下文章完整地阐述了它们，包括上一句： http://msdn.microsoft.com/en -us / library / ms189799.aspx [ ^ ]

Use an after insert/update/delete trigger.

I presume that other rows may be affected after an insert, update or delete, so a trigger is the best way to go. A trigger ensures that if there are multiple sources of of action queries (direct table edit, multiple web pages/applications), the code always fires.

One last general "best practice" rule is not to store derived data, but to derive minimum values on the fly. Like most "rules" there may be exceptions, but I advise you to consider best practice before compromising. At least a trigger ties the derived data to the original data and should avoid data inconsistencies.

You can pretty much use any T-SQL in a trigger, including calling stored procedures, but avoid circular actions/cascading/nested triggers. The following article expalins them in full, including the previous sentence: http://msdn.microsoft.com/en-us/library/ms189799.aspx[^]

您可以先添加值，然后将其更改为主键

You can add the values first and then change it to primary key

这篇关于在检查SQL Server表中的某些值后，如何在特定列中插入0和1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！