Hi,
I noticed one thing here.
Your table which is updated (UMTranImpoPendLog) is joined with "LEFT OUTER JOIN".
Can you please check the select result by replacing LEFT OUTER JOIN to INNER JOIN once ?
I am sure the result will be blank.
SELECT B.CommHeirID,D.CommHeirID
FROM DBAMFDISTData.DBO.UMCommWithHeldLog A
INNER JOIN DBAMFDISTData.DBO.UMTranImpoPendLog B ON A.TranNumb2 = B.TranNumb2
LEFT OUTER JOIN BAMASTER C ON A.SubBroker = C.CustID
LEFT OUTER JOIN vwEffectiveCommUserMastIDs D ON C.BAID = D.BAID
WHERE ISProcessed=0
and b.CommHeirID is null and d.commheirid is not null
Please check row count of the above query.
If the result is "0 rows affected" then the updated result is correct.
In update query only those rows will get updated which will returned from the select statement.
If you get all the rows NULL of the table which is updated in LEFT JOIN then that is not the row. It's just a NULL value. If it's not a row of the updated table then what will get updated ? I guess nothing. :)
I have tried the same with tables of my database and got the same result.
Please let me know if you have any concern or query on this or if I am not able to explain you properly.
Thanks