SQL根据特定条件更新一列

I have a SQL table and within this table there is a BIT column "IsComplete" that needs to be set if other columns have data within them.

我有一个SQL表，在这个表中有一个BIT列“IsComplete”，如果其他列中包含数据，则需要设置它。

The IsComplete was originally handled by a checkbox in the application, and I want to transfer the responsibility to the SQL DB to handle setting this "IsComplete" column to true based on the following:

IsComplete最初由应用程序中的复选框处理，我想将责任转移到SQL DB，以根据以下内容将此“IsComplete”列设置为true：

IsComplete = true if column1 = [something] and column2 = [something] and column3 = [something]
IsComplete = false if column1, column2 or column3 is null or = [nothing]

How do I accomplish this.

我该如何做到这一点。

5 个解决方案

#1

You could Also have a computed column for this.

你也可以有一个计算列。

Just create a Function to receive the 3 paranmeters and return True if 3 parameters are not null and else False.

只需创建一个函数来接收3个参数，如果3个参数不为空，则返回True，否则返回False。

Then Create the column IsCOmplete ,and set it in Computed Column Specification

然后创建列IsCOmplete，并将其设置在计算列规范中

In formula use dbo.Yourfunction(column1,column2,column3)

在公式中使用dbo.Yourfunction（column1，column2，column3）

#2

You can just do a Update

你可以做一个更新

Update tableBlah setComplete = 0

Update tableBlah setComplete = 1 where column1 = [something] and column2 = [something] and column3 = [something]

And have a trigger on that table to do the same when inserting.

并且在插入时在该表上有一个触发器来执行相同的操作。

#3

update Table SET IsComplete = ISNULL(DATALENGTH(COALESCE(col1,col2,col3)) % 1) + 1,0);

Should do the trick.

应该做的伎俩。

什么是合并

什么是isnull

什么是数据长度

什么是％

#4

I'm not sure about your question, I think I'm missing something. Anyway, if it's what I think, You can use Case statement

我不确定你的问题，我想我错过了什么。无论如何，如果这是我的想法，你可以使用Case语句

update T set IsComplete = CASE WHEN column1 = something and column2 = something
and column2 = something THEN 1 
WHEN column1 != [Something] OR column2 != [Something] OR column3 != [Something] THEN 0 END
from yourTable T --Your where criteria goes Here, if you need it

This answer consider that yourTable got 3 columns, and a column Something, and you will update IsComplete considering the values on each row

这个答案认为youTable有3列，并且列有Something，考虑到每行的值，你会更新IsComplete

#5

-1

Use Case

用例

SET IsComplete = CASE 
WHEN NULLIF(column1,'') IS NULL OR NULLIF(column2,'') is null 
or NULLIF(column3,'') is null THEN 0 
ELSE 1   
END

Answer to your comment below for INSERT:

对INSERT的评论如下：

INSERT INTO TABLE1 (IsComplete) 
SELECT CASE 
    WHEN NULLIF(@column1,'') IS NULL OR 
    NULLIF(@column2,'') is null or 
    NULLIF(@column3,'') is null THEN 0 
    ELSE 1     
    END

#1