在 SQL 更新语句中使用 IIF
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/14898151/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using IIF in SQL update statement
提问by Joe Yan
My existing SQL statement
我现有的 SQL 语句
UPDATE EMP
SET fte_adj = IIf((fte<1 Or fte Is Null) And [employment category] Like '*Full-Time*',1,
IIf((fte=0 Or fte Is Null) And [employment category] Like '*Part-Time*',0.25,fte));
it will update table EMP
它将更新表 EMP
set fte_adj=1
if Full time (and fte<1 or null)
set fte_adj=0.25
if part time (and fte=0 or null)
otherwise fte_adj=fte
How can I add a case to check is there any records for the employee_id exists in another table SEC_EMP
?
如何添加一个案例来检查另一个表中SEC_EMP
是否存在该 employee_id 的任何记录?
if there is no records (0 row), then set fte_adj=1
如果没有记录(0行),则 set fte_adj=1
both table can use employee_id as key
两个表都可以使用employee_id作为键
thanks
谢谢
采纳答案by sgeddes
I've rewritten your statement using CASE (but I realize IIF works in 2012):
我已经使用 CASE 重写了您的声明(但我意识到 IIF 在 2012 年有效):
UPDATE EMP
SET fte_adj =
CASE WHEN (FTE < 1 OR FTE IS NULL) AND [employment category] Like '*Full-Time*'
THEN 1
ELSE
CASE WHEN (FTE = 0 OR FTE IS NULL) AND [employment category] Like '*Part-Time*'
THEN 0.25
ELSE
CASE WHEN (SELECT COUNT(*) FROM SEC_EMP) = 0
THEN 1
ELSE
FTE
END
END
END
And here is some sample Fiddle.
这是一些示例Fiddle。
If you prefer the IIF logic, here you go:
如果您更喜欢 IIF 逻辑,请访问:
UPDATE EMP
SET fte_adj =
IIF ( (FTE < 1 OR FTE IS NULL) AND [employment category] Like '*Full-Time*' , 1 ,
IIF ( (FTE = 0 OR FTE IS NULL) AND [employment category] Like '*Part-Time*' , 0.25,
IIF ( (SELECT COUNT(*) FROM SEC_EMP) = 0 , 1 , FTE )
)
)
And more Fiddle.
还有更多的小提琴。
--EDIT--
- 编辑 -
If I'm understanding latest comment, you want to update records that ONLY exist in the SEC_EMP table? If so, just JOIN to the table as such:
如果我了解最新评论,您想更新仅存在于 SEC_EMP 表中的记录吗?如果是这样,只需像这样加入表:
UPDATE E
SET fte_adj =
IIF ( (FTE < 1 OR FTE IS NULL) AND [employment category] Like '*Full-Time*' , 1 ,
IIF ( (FTE = 0 OR FTE IS NULL) AND [employment category] Like '*Part-Time*' , 0.25, FTE
)
)
FROM EMP E
JOIN SEC_EMP SE ON E.employee_id = SE.employee_id
And more Fiddle.
还有更多的小提琴。
回答by RandomUs1r
Try this:
尝试这个:
UPDATE EMP
SET fte_adj = CASE WHEN fte=1 Or fte Is Not Null And [employment category] Like '%Full-Time%'
THEN 1 ELSE .25 END AS 'Rate'
I'd also add
我还要补充
WHERE [employment category] Like '%Full-Time%' OR [employment category] Like '%Part-Time%'
For safety depending on your table. This is for MS SQL btw, no idea what '* syntax *' is from.
为安全起见,取决于您的餐桌。这是用于 MS SQL 顺便说一句,不知道 '* 语法 *' 来自什么。
回答by Kenneth Fisher
Simpler version of the case statement from sgeddes.
来自 sgeddes 的 case 语句的更简单版本。
UPDATE EMP
SET fte_adj =
CASE WHEN (FTE < 1 OR FTE IS NULL) AND [employment category] Like '*Full-Time*'
THEN 1
WHEN (FTE = 0 OR FTE IS NULL) AND [employment category] Like '*Part-Time*'
THEN 0.25
WHEN (SELECT COUNT(*) FROM SEC_EMP) = 0
THEN 1
ELSE
FTE
END
Case statements unlike IFs can have multiple branches. They also short circuit so the first branch that get's hit is it. So for example
与 IF 不同的 Case 语句可以有多个分支。它们也会短路,因此第一个受到攻击的分支就是它。所以例如
CASE WHEN 1=1
WHEN 2=2
END
Will never go past the 1=1 branch.
永远不会超过 1=1 分支。
回答by Mike Perrenoud
You could add an IIF
, where ever it's relevant, with an EXISTS
statement:
您可以IIF
在任何相关的地方添加一个带有EXISTS
声明的 :
IIF(NOT EXISTS(
SELECT [some column] FROM [some table]
WHERE [some condition]
), 1, [some value OR another IIF statement])