在SQL更新语句中使用IIF

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行),则设置fte_adj = 1

both table can use employee_id as key

两个表都可以使用employee_id作为密钥

thanks

4 个解决方案

#1

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.

这里有一些小提琴样本。

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.

还有更多小提琴。

#2

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 btw,不知道'* syntax *'来自哪个。

#3

You could add an IIF, where ever it's relevant, with an EXISTS statement:

您可以使用EXISTS语句添加IIF,无论它与哪些相关:

IIF(NOT EXISTS(
    SELECT [some column] FROM [some table]
    WHERE [some condition]
), 1, [some value OR another IIF statement])

#4

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 WHEN 1=1
WHEN 2=2
END

Will never go past the 1=1 branch.

永远不会超过1 = 1分支。