This might be what you want, but I'm guessing on the join:
--=========== TEST DATA ===========================
-- Reset all NOT NULL to NULL...
--Source Table:
if object_id('[dbo].[CMC_BLCO_COMM_ITEM]') is not null drop table [dbo].[CMC_BLCO_COMM_ITEM]
CREATE TABLE [dbo].[CMC_BLCO_COMM_ITEM](
[BLEI_CK] [int] NULL,
[CSPI_ID] [char](8) NULL,
[PMFA_ID] [char](2) NULL,
[BLCO_SEQ_NO] [smallint] NULL,
[COAR_ID] [char](12) NULL,
[BLCO_EFF_DT] [datetime] NULL,
[BLCO_TERM_DT] [datetime] NULL,
[BLCO_PCT] [int] NULL,
[ROW_MODIFICATION_TYPE] [char](1) NULL,
[CR_RUN_CYC_EXCTN_SK] [int] NULL,
[LST_UPDT_RUN_CYC_EXCTN_SK] [int] NULL
) ON [PRIMARY];
insert [dbo].[CMC_BLCO_COMM_ITEM] ([BLEI_CK], [CR_RUN_CYC_EXCTN_SK]) values ( null, 1 );
insert [dbo].[CMC_BLCO_COMM_ITEM] ([BLEI_CK], [CR_RUN_CYC_EXCTN_SK]) values ( 96, 2 );
insert [dbo].[CMC_BLCO_COMM_ITEM] ([BLEI_CK], [CR_RUN_CYC_EXCTN_SK]) values ( 97, 3 );
--Target Table:
if object_id('[dbo].[BILL_CMSN]') is not null drop table [dbo].[BILL_CMSN]
CREATE TABLE [dbo].[BILL_CMSN](
[BILL_CMSN_SK] [int] NULL,
[SRC_UNQ_BILL_ENTY_ID] [int] NULL,
[PDT_PLAN_ID] [varchar](50) NULL,
[FEE_DISC_ID] [varchar](50) NULL,
[BILL_CMSN_SEQ_NUM] [smallint] NULL,
[SRC_SYS_CD] [varchar](30) NULL,
[CR_RUN_CYC_EXCTN_SK] [int] NULL,
[LST_UPDT_RUN_CYC_EXCTN_SK] [int] NULL,
[CRD_DTTM] [datetime] NULL,
[UPDT_DTTM] [datetime] NULL,
[SRC_SYS_CD_SK] [int] NULL,
[BILL_ENTY_SK] [int] NULL,
[PDT_PLAN_SK] [int] NULL,
[CMSN_ARGT_SK] [int] NULL,
[BILL_CMSN_EFF_DT_SK] [int] NULL,
[BILL_CMSN_TRMN_DT_SK] [int] NULL,
[BILL_CMSN_PREM_PCT] [int] NULL,
) ON [PRIMARY]
insert [dbo].[BILL_CMSN] ([BILL_ENTY_SK], [CR_RUN_CYC_EXCTN_SK]) values ( 0, 1 );
insert [dbo].[BILL_CMSN] ([BILL_ENTY_SK], [CR_RUN_CYC_EXCTN_SK]) values ( 0, 2 );
insert [dbo].[BILL_CMSN] ([BILL_ENTY_SK], [CR_RUN_CYC_EXCTN_SK]) values ( 0, 3 );
--Look Up Table:
if object_id('[dbo].[BILL_ENTY]') is not null drop table [dbo].[BILL_ENTY]
CREATE TABLE [dbo].[BILL_ENTY](
[BILL_ENTY_SK] [int] NULL,
[SRC_UNQ_BILL_ENTY_ID] [int] NULL,
[SRC_SYS_CD] [varchar](30) NULL,
[CR_RUN_CYC_EXCTN_SK] [int] NULL,
[LST_UPDT_RUN_CYC_EXCTN_SK] [int] NULL,
[CRD_DTTM] [datetime] NULL,
[UPDT_DTTM] [datetime] NULL,
[SRC_SYS_CD_SK] [int] NULL,
[BILL_GRP_SK] [int] NULL,
[GRP_SK] [int] NULL,
[SUB_GRP_SK] [int] NULL,
[SUBS_SK] [int] NULL,
[ALT_FUNDG_AGRE_SK] [int] NULL,
[BILL_LVL_CD_SK] [int] NULL,
[BILL_ENTY_LST_BILL_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_BILL_END_DT_SK] [int] NULL,
[BILL_ENTY_LST_APV_BILL_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_BILL_PMT_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_TOLRNC_PAID_BILL_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_RCONCL_BILL_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_BILL_GNT_DT_SK] [int] NULL,
[BILL_ENTY_LST_MLTP_MODE_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_MLTP_MODE_END_DT_SK] [int] NULL,
[BILL_ENTY_BILL_BAT_STU_CD_SK] [int] NULL,
[BILL_ENTY_LST_BILL_MNL_ALLOCS_DUE_DT_SK] [int] NULL,
[BILL_ENTY_PRCS_STU_CD_SK] [int] NULL,
[BILL_ENTY_SSPND_BILL_CD_SK] [int] NULL,
[BILL_ENTY_LST_ARCV_DT_SK] [int] NULL,
[BILL_ENTY_ELIG_THRU_DT_UPDT_CD_SK] [int] NULL,
[BILL_ENTY_TOLRNC_METH_CD_SK] [int] NULL,
[BILL_ENTY_NET_DUE_AMT] [money] NULL,
[BILL_ENTY_NET_CURR_OUTSTND_SUSP_AMT] [money] NULL,
[BILL_ENTY_RCONCL_IND] [char](1) NULL,
[BILL_ENTY_SUBS_RATNG_IND] [char](1) NULL,
[BILL_ENTY_RMTC_TOLRNC_AMT] [money] NULL,
[BILL_ENTY_RMTC_TOLRNC_PCT] [int] NULL,
[SRC_UNQ_BILL_LVL_ID] [int] NULL
) ON [PRIMARY]
insert [dbo].[BILL_ENTY] ([BILL_ENTY_SK], [SRC_UNQ_BILL_ENTY_ID]) values ( 33, 96 );
--Condition:
--I am working on DW project where I need to validate if the data from the Source Column
-- has populated correctly on the Target Column based on a lookup condition. I have given the
-- create table statements for the Source table,Target table and the Lookup table above.
-- THe actual columns used in this condition are highlighted. The condition is given below,
--If CMC_BLCO_COMM_ITEM.BLEI_CK is NULL then set BILL_ENTY_SK = -1
--ELSE IF CMC_BLCO_COMM_ITEM.BLEI_CK is NULL THEN Perform a LOOKUP on
--BILL_ENTY WHERE BILL_ENTY.SRC_UNQ_BILL_ENTY_ID = CMC_BLCO_COMM_ITEM.BLEI_CK and
--RETURN BILL_ENTY.BILL_ENTY_SK
--ELSE IF Lookup fails then set BILL_ENTY_SK = -1
--I need some help to create a SQL statement which will validate if the data from the source column has
-- populated correctly on the table column based on the above condition.
--========= CHECK BEFORE UPDATES =============
select [CR_RUN_CYC_EXCTN_SK], [BILL_ENTY_SK] from [dbo].[BILL_CMSN];
--========= SUGGESTED SOLUTION ================
UPDATE [dbo].[BILL_CMSN]
SET BILL_ENTY_SK = CASE WHEN CI.BLEI_CK IS NULL THEN -1
WHEN BE.BILL_ENTY_SK IS NULL THEN -1
ELSE BE.BILL_ENTY_SK END
FROM [dbo].[BILL_CMSN] BC
INNER JOIN [dbo].[CMC_BLCO_COMM_ITEM] CI
ON BC.[CR_RUN_CYC_EXCTN_SK] = CI.[CR_RUN_CYC_EXCTN_SK] -- ????? WHAT'S THE JOIN ???????
LEFT OUTER JOIN dbo.BILL_ENTY BE
ON BE.SRC_UNQ_BILL_ENTY_ID = CI.BLEI_CK;
--========= CHECK AFTER UPDATES =============
select [CR_RUN_CYC_EXCTN_SK], [BILL_ENTY_SK] from [dbo].[BILL_CMSN];