Conditionalize field values based on other field values

Here's a portion of the current statement.

UPDATE EngagementAuditAreas

SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,

numInherentRiskID = parent.numInherentRiskID,

numControlRiskID = parent.numControlRiskID,

numCombinedRiskID = parent.numCombinedRiskID,

numApproachTypeId = parent.numApproachTypeId,

bInherentRiskIsAffirmed = 0,

bControlRiskIsAffirmed = 0,

bCombinedRiskIsAffirmed = 0,

bApproachTypeIsAffirmed = 0,

bCommentsIsAffirmed = 0

FROM EngagementAuditAreas WITH(NOLOCK) ...

And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).

UPDATE EngagementAuditAreas

SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,

numInherentRiskID = parent.numInherentRiskID,

numControlRiskID = parent.numControlRiskID,

numCombinedRiskID = parent.numCombinedRiskID,

numApproachTypeId = parent.numApproachTypeId,

bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),

bControlRiskIsAffirmed = (numControlRiskID IS NULL),

bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),

bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),

bCommentsIsAffirmed = (parent.txtComments IS NULL)

FROM EngagementAuditAreas WITH(NOLOCK)

Thanks.

Here is a small example of how you might accomplish your task.

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
Affirmed char(4),
Num int
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( NULL, 1 )
INSERT INTO @.MyTable VALUES ( NULL, 0 )
INSERT INTO @.MyTable VALUES ( NULL, NULL )

UPDATE @.MyTable
SET Affirmed = CASE Num
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE 'n/a'
END

SELECT *
FROM @.MyTable

RowID Affirmed Num
-- -- --
1 No 1
2 Yes 0
3 n/a NULL

However, it is usually NOT a good idea to have two columns that contain the same information (even if in two forms). You can easily 'transform' the values in the select queries using the same CASE structure as above.