Using IIF in SQL update statement
26,688
Solution 1
I've rewritten your statement using CASE (but I realize IIF works in 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:
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:
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.
Solution 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.
Related videos on Youtube
Author by
Joe Yan
Updated on February 16, 2020Comments
-
Joe Yan over 4 years
My existing SQL statement
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
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
?if there is no records (0 row), then
set fte_adj=1
both table can use employee_id as key
thanks
-
Mark Kram over 11 yearsHave you thought of using a Case statement instead?
-
-
Lamak over 11 yearsHis current sql query is most likely for ms-access
-
sgeddes over 11 years@Lamak -- I'd guess SQL Server 2012
-
Lamak over 11 years@sgeddes that's also an option
-
Joe Yan over 11 yearsSorry that there is missing in my question. I need to check is there any rows for the employee_id exists in another table "SEC_EMP". is it true that i add a WHERE clause at the end that EMP.employee_id = SEC_EMP.employee_id ?
-
sgeddes over 11 years@JoeYan -- so you only want to update records that exist in SEC_Emp -- easy enough if that's the case -- let me know and I'll update the answer using a JOIN.
-
sgeddes over 11 years@JoeYan -- I've updated my answer -- that will update only users that match in the SEC_EMP table. Hope this helps (and that I understood your requirements correctly).
-
Joe Yan over 11 yearsthanks sgeddes. it is true that i want to update records that ONLY exist in the SEC_EMP table.
-
sgeddes over 11 years@JoeYan -- no problem -- glad I could help!
-
Joe Yan over 11 yearsFor using CASE statement, I put the JOIN table statement after the CASE END. UPDATE EMP SET fte_adj = CASE WHEN XXX END FROM EMP A INNER JOIN SEC_EMP B ON A.EMPLOYEE_ID=B.EMPLOYEE_ID