SQL Server 2008合并软删除错误

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

我正在尝试使用SQL Server 2008 MERGE命令对目标表中的一行执行软删除。

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.

我认为这应该属于“当与源不匹配”部分时,因为源缺少行并且目标仍然具有它。我想要做的就是将IsActive位设置为false,但是我收到了错误。

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

“尝试将非NULL-able列的值设置为NULL。”

What am I missing?

我错过了什么?

The Users table is:

Users表是:

[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;

2 个解决方案

#1

You can get this to work exactly as you want but for me I needed to add a condition in the NOT MATCHED line.

你可以完全按照自己的意愿使用它,但对我来说,我需要在NOT MATCHED行中添加一个条件。

So try something like...

所以试试像......

WHEN NOT MATCHED BY SOURCE 
                    AND TARGET.[IsActive] = 1 
                    AND TARGET.[DeletedOn] IS NULL 
    THEN UPDATE
    SET 
      TARGET.[IsActive] = 0, 
      TARGET.[DeletedOn] = SYSDATETIMEOFFSET()

#2

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

您的临时表TempUserTable似乎在IsActive列或ID列中具有NULL。

#1