sql - 无法将值 NULL 插入列 - 但不存在空值

Question

我有以下 CTE更新 lTesterID表中的列Employee从表提供者中随机选择的有效 ID。

但我收到这个错误

Cannot insert the value NULL into column 'lTesterID ', table 'Employee'; column does not allow nulls. UPDATE fails.

即使没有 Null值，因为我在 cteTable4 中排除了它.

有人可以指出我正确的方向吗？

WITH    cteTable3
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY NEWID() ) AS n ,
                        lTesterID
                        FROM     Employee
               WHERE lTesterID= 0 
             ),
        cteTable4
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY NEWID() ) AS n ,
                        ISNULL(lTesterID,0) AS lTesterID
               FROM     Provider
               WHERE    Active = 'True'
                        AND lTesterID IS NOT NULL
             )
    UPDATE  cteTable3
    SET     lTesterID = ( SELECT  lTesterID
                            FROM    cteTable4
                            WHERE   cteTable3.n = cteTable4.n
                AND lTesterID IS NOT NULL
                          )

Answer 1

您正在加入 CTE cteTable3.n = cteTable4.n在相关子查询中。 cteTable3.lTesterID将更新为 NULL其中子查询返回零行。如果从 cteTable4 返回的行数较少，就会发生这种情况。比您来自 cteTable3 .