SQL Server 2008 Merge Soft Delete Error

Question

I'm trying to perform a soft delete on a row in my target table using the SQL server 2008 MERGE command.

I think this should fall under the "when not matched by source" section, since the source is missing the row and the target still has it. All I want to do is set the IsActive bit to false, but I'm getting an error.

"Attempting to set a non-NULL-able column's value to NULL."

What am I missing?

The Users table is:

[ID] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](200) NULL,
[LastName] [nvarchar](200) NULL,
[EmailAddress] [nvarchar](200) NULL,
[IsActive] [bit] NOT NULL

The Merge statement is:

merge into Users
using TempUserTable lu
on Users.ID = TempUserTable.ID
when matched then
update set
    ID = lu.ID,
    FirstName = lu.FirstName,
    LastName = lu.LastName,
    EMailAddress = lu.EmailAddress,
    IsActive = lu.Status

when not matched then
    insert (ID, FirstName, LastName, EmailAddress, IsActive)
    values (lu.ID, lu.FirstName, lu.LastName, lu.EmailAddress, lu.Status)
when not matched by source then
    update set  IsActive = 0;

Answer 1

It appears that your temp table TempUserTable either has a NULL in the IsActive column or the ID column.