Cannot insert the value NULL into column - But No Null values exist

Question

I have the following CTE to update lTesterID column in table Employee to a valid ID randomly chosen from the table provider.

But I get this error

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

Even though there are no Null values as I'm excluding it in cteTable4.

Can someone point me in the right direction?

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
                          )

Thanks Mikael. It all makes sense now.

Nas
– Nas

2014-03-11 06:42:50 +00:00
Commented Mar 11, 2014 at 6:42 — Nas
– Nas, Commented Mar 11, 2014 at 6:42

Mikael Eriksson · Accepted Answer · 2014-03-11 07:15:46Z

2

You are joining the CTE's on cteTable3.n = cteTable4.n in a correlated subquery. cteTable3.lTesterID will be updated with NULL where the subquery returns zero rows. That will happen if you have less rows returned from cteTable4 than you have from cteTable3.

answered Mar 11, 2014 at 7:15

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot insert the value NULL into column - But No Null values exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related