如何为预定义值和非预定义值编写更新过程 [英] How to write update procedure for predefined values and non-predefined values

问题描述

我有一个列名列帐户名的表。我有一些预定义的帐户值，如bankAC，loanAc，salesAc等等。我正在用帐户名更新我的帐户表。并且不允许删除此预定义帐户。只允许用户创建的帐户删除。我有范围标识的id。





i希望用户创建的组名（帐户组名）应该更新，并且不应更新预定义的组名。



我尝试了什么：



i有这个更新程序



BEGIN



- 设置NOCOUNT ON;

UPDATE tbl_AccountGroup SET Group_name = @Group_name，Comments = @Comments，chbox = @chbox，Under_Group = @Under_Group

WHERE Group_name = @Group_name;

END

解决方案

您描述更新的方式没有任何意义，但如果您不想更新某些记录，则需要在WHERE子句中排除它们。

 更新 table1 
  SET  field1 =  @ field1  
  WHERE  group_name  NOT   IN （'  one'，'  two'，' 三个）

我也会建议在该表中添加一个标志列，一个位可以工作，1表示它是一个系统记录，不能被触摸，0表示它是由用户创建的。这将使得查找它们变得更加容易。

我得到的结果是您的更新无法正常工作，因为约束阻止了它，您可以尝试以下操作（首先进行备份）：

  ALTER   TABLE  tbl_AccountGroup  NOCHECK   CONSTRAINT   ALL  
   -   做你的事，例如 
  DROP   TABLE  tbl_AccountGroup 
  ALTER   TABLE  tbl_AccountGroup  CHECK   CONSTRAINT   ALL  

i have one table with column name account names.i have some predefined values for account like bankAC,loanAc,salesAc etc etc. i am updating my account table with account names. and this predefined accounts are not allowed to delete.only user created accounts are allowed to delete. and i have id with scope identity.


i want user created group name(Account group names)should be updated and predefined group names should not be updated.

What I have tried:

i have this update procedure

BEGIN

--SET NOCOUNT ON;
UPDATE tbl_AccountGroup SET Group_name = @Group_name, Comments = @Comments, chbox = @chbox, Under_Group= @Under_Group
WHERE Group_name = @Group_name;
END

解决方案

The way you have described your update does not make any sense, but if you do not want to update certain records then you need to exclude them in a WHERE clause.

UPDATE table1 
SET field1 = @field1
WHERE group_name NOT IN('one', 'two', 'three')

I would also suggest adding a flag column to that table, a bit would work, and 1 means it is a system record and cannot be touched and 0 means it was created by user. It will make finding them much easier.

I get the impression your update won't work because a constraint is blocking it, you could try the following (make a backup first):

ALTER TABLE tbl_AccountGroup NOCHECK CONSTRAINT ALL
-- Do your thing, e.g.
DROP TABLE tbl_AccountGroup
ALTER TABLE tbl_AccountGroup CHECK CONSTRAINT ALL

这篇关于如何为预定义值和非预定义值编写更新过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！